Help selecting top 10 values starting from highest by location

Database
Visitor

Help selecting top 10 values starting from highest by location

 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.

 

2 REPLIES
Highlighted
Teradata Employee

Re: Help selecting top 10 values starting from highest by location

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

Junior Contributor

Re: Help selecting top 10 values starting from highest by location

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