CODEWITHSUNDEEP
sqloracle-database
Andrew Salib
Andrew Salib

Reputation: 115

SQL Query to show highest sales in a City for EACH State

SELECT c.STATE, c.CITY, SUM(s.QTY * s.SALEPRICE)
FROM DWSALE s
INNER JOIN DWCUST c
ON s.dwcustid = c.dwcustid
GROUP BY c.STATE, c.CITY
ORDER BY c.STATE;

This current query above shows the TOTAL (SUM) amount of sales made (sale = qty * saleprice) for each city while also showing the state it resides in.

Clients (found in DWCUST) live in Cities and States. Sales are recorded in DWSALE and hold the sales info.

I need the query to show the cities with the HIGHEST sales for EACH of their respective states. I am not sure what to do.

Any help is appreciated!

Upvotes: 0

Views: 8096

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can use ROW_NUMBER() or RANK() to get what you want:

SELECT state, city, total
FROM (SELECT c.STATE, c.CITY, SUM(s.QTY * s.SALEPRICE) as total,
             ROW_NUMBER() OVER (PARTITION BY c.STATE ORDER BY SUM(s.QTY * s.SALEPRICE)) as seqnum
      FROM DWSALE s INNER JOIN 
           DWCUST c
           ON s.dwcustid = c.dwcustid
      GROUP BY c.STATE, c.CITY
     ) sc
WHERE seqnum = 1
ORDER BY STATE;

If you want ties (multiple rows for the same state), then use RANK() instead of ROW_NUMBER().

Upvotes: 4

clq
clq

Reputation: 180

with statecitytotal as
(SELECT c.STATE, c.CITY, SUM(s.QTY * s.SALEPRICE) total
FROM DWSALE s
INNER JOIN DWCUST c
ON s.dwcustid = c.dwcustid
GROUP BY c.STATE, c.CITY
ORDER BY c.STATE)
select * from (select state, city, total, rank() over(partition by state order by total desc) rnk from statecitytotal) where rnk=1

but, if total for 2 cities is the same then both are shown

Upvotes: 1

Related Questions