luftlinie luftlinie - 3 months ago 22
SQL Question

MS Access 2010 SQL Top 3 Sales in Group by

I have a table with columns such as year, quarter, team and price. it shows every sale from the team. We have 10 teams in the firm.
I want to know for each quarter of each year, which three teams sold the most overall. So where am I stuck? At this point I grouped the total sales per team, per quarter, per year. Still missingt o only get the three best teams with highest sales. I got this so far:

SELECT
a1.year,
a1.quarter,
a1.team,
sum(a1.price) as Total

FROM
tbl_sales a1

inner JOIN
tbl_sales a2
ON a1.year = a2.year
and a1.quarter = a2.quarter
and a1.team = a2.team
and a1.price = a2.price
where
some restrictions here
GROUP BY
a1.year,
a1.quarter,
a1.team


Something tells me I am close, that just a subquery with the top function will help but I just can't figure it out. Any help is very much appreciated :)
Thanks a lot!

Answer

Try:

SELECT TOP 3
    a1.year,
    a1.quarter,
    a1.team,
    sum(a1.price) as Total

FROM
    tbl_sales a1    

inner JOIN
    tbl_sales a2 
        ON a1.year = a2.year
            and a1.quarter = a2.quarter
            and a1.team = a2.team
            and a1.price = a2.price
     where
           some restrictions here
GROUP BY
    a1.year,
    a1.quarter,
    a1.team

ORDER BY 
    sum(a1.price) DESC

Edit, try the following, you might need additional criteria to ensure you get the correct resultset but this should return the top 3 of each quarter that is specified in the where clause, I don't know why you are using a self join so I left that out but it can be added back in if necessary:

SELECT TOP 3
    a1.year,
    a1.quarter,
    a1.team,
    sum(a1.price) as Total

FROM
    tbl_sales a1

WHERE
     'some restrictions here'
     AND a1.quarter = 1 --(or however quarters are identified)
     AND a1.year = 2015 --(or however years are identified)

GROUP BY
    a1.year,
    a1.quarter,
    a1.team

ORDER BY 
    sum(a1.price) DESC

UNION ALL

SELECT TOP 3
    a2.year,
    a2.quarter,
    a2.team,
    sum(a2.price) as Total

FROM
    tbl_sales a2

WHERE
    'some restrictions here'
    AND a2.quarter = 2
    AND a2.year = 2015

GROUP BY
    a2.year,
    a2.quarter,
    a2.team

ORDER BY 
    sum(a2.price) DESC

UNION ALL

SELECT TOP 3
    a3.year,
    a3.quarter,
    a3.team,
    sum(a3.price) as Total

FROM
    tbl_sales a3

WHERE
    'some restrictions here'
    AND a3.quarter = 3
    AND a3.year = 2015

GROUP BY
    a3.year,
    a3.quarter,
    a3.team

ORDER BY 
    sum(a3.price) DESC

UNION ALL

SELECT TOP 3
    a1.year,
    a1.quarter,
    a1.team,
    sum(a1.price) as Total

FROM
    tbl_sales a4

WHERE
    'some restrictions here'
    AND a4.quarter = 4
    AND a4.year = 2015

GROUP BY
    a4.year,
    a4.quarter,
    a4.team

ORDER BY 
    sum(a4.price) DESC

As you can see there is basically no difference between the tables except the where clause, you aren't limited to a union of just those 4 tables, it is just an example but just keep in mind that if you need to cover more quarters you will need to add more tables to be unioned. This can get very unwieldy as it requires you to keep adding tables to the union as time goes on.

Comments