'How to return the Max of a Sum?

I have 2 Tables and in one of them (T) there's Sales for each transaction and in other Table (S) is state for each Transaction. the S.S is the Id which connects 2 tables.

What I have is

select sum(T.SALE_PRICE) as 'Sales of each state',S.STATE
  from T
  inner join S on T.S=S.S
  group by S.STATE

Which successfully returns the sum for each individual state. the difference between my question and other ones is that I need to join the tables to get the State and Sum(T.SALE_PRICE) which makes it hard.

How can I find out which state has the most sales?

sqlsql-serverjoinsubquery


Solution 1:[1]

You can try to use TOP 1 and ORDER BY

select TOP 1 sum(T.SALE_PRICE) as 'Sales of each state',S.STATE
FROM T
inner join S on T.S=S.S
group by S.STATE
ORDER BY sum(T.SALE_PRICE) DESC

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 D-Shih

Related Questions