SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    career sales query

    I have a table of sales for each employee. Each row represents data from a month, so one employee could have many rows.

    I want to know a list of all employee who sold over 1 million widgets. How would I do that select?

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Easier to answer if you tell us what the tables look like

    OK, let's say you have this Sales table:
    year,month,employee_id,amount
    2002,8,1,499999
    2002,8,2,500001
    2002,8,3,200000
    2002,9,1,200000
    2002,9,2,300000
    2002,9,3,400000
    2002,10,1,300000
    2002,10,2,200000
    2002,10,3,400000

    To get the employees who sold over 1M (between august & october this year):
    select e.firstname, sum(s.amount)
    from sales s inner join employee e on s.employee_id = e.id
    where (s.year = 2002) and (s.month between 8 and 10)
    group by s.employee_id
    having sum(s.amount) >= 1000000
    order by 2 desc

    Result:
    firstname,sum(s.amount)
    Jane,1000001
    Joe,1000000


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •