luftlinie - 1 year ago 59

SQL Question

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 Source

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.