Good afternoon,
I have a data set from 25 locations with daily quantity and dollar sales for a year. I am trying to pull the top 10 sales dates based on dollar sales sorted by high to low by location.
SELECT TOP 10 A.LOCATION_ID, A.SALES_DATE,A.SALES_DOLLARS FROM ( LOCATION_ID AS LOCATION_ID, SALES_DATE AS SALES_DATE, SUM(QUANTITY_SOLD) AS TOTAL_QTY, SUM(DOLLARS_SOLD) AS SALES_DOLLARS WHERE SALES_DATE BETWEEN '1-1-2017' AND '12-16-2017' GROUP BY 1,2 ) AS A
This query only provides top 10 locations based on dollars. I tried using some of the examples in this forum and could find one that matched what I need.
Any help will be greatly appreciated!!!
Thanks.
Something like this, I think:
SELECT A.LOCATION_ID
,A.SALES_DATE
,A.TOTAL_QTY
,A.SALES_DOLLARS
FROM
(SELECT LOCATION_ID AS LOCATION_ID
,SALES_DATE AS SALES_DATE
,SUM(QUANTITY_SOLD) AS TOTAL_QTY
,SUM(DOLLARS_SOLD) AS SALES_DOLLARS
WHERE SALES_DATE BETWEEN '1-1-2017'
AND '12-16-2017'
GROUP BY 1 ,2 ) A
QUALIFY Rank() Over(Partition by Location_ID Order by SALES_DOLLARS Desc) <= 10
As OLAP functios are calculated after aggregates this can be further simplified:
SELECT LOCATION_ID AS LOCATION_ID ,SALES_DATE AS SALES_DATE ,SUM(QUANTITY_SOLD) AS TOTAL_QTY ,SUM(DOLLARS_SOLD) AS SALES_DOLLARS WHERE SALES_DATE BETWEEN '1-1-2017' AND '12-16-2017' GROUP BY 1 ,2 QUALIFY Rank() -- maybe ROW_NUMBER instead (TOP/ROW_NUMBER vs TOP WITH TIES/RANK) Over(Partition by Location_ID Order by SALES_DOLLARS Desc) <= 10