How to return the Max of a Sum?-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

select MAX([Sales of each state]), STATE
FROM (
  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
) TEMP 
group by STATE