top value dimension based on sum in SQL

Question

i am having an issue with a query to bring back the top type of transaction for each location:

my table has location varchar, type varchar, transaction int

i've tried the following and get errors:

select  
c.location, 
c.type,
sum(c.transaction) as trans_sum
from sales c
group by c.location, c.type
having trans_sum = (
select top 1
c2.type, sum(c2.transaction) as trans_sum2
from sales c2
where c2.location = c.location
group by c2.location, c2.type
order by c2.location, trans_sum2 desc
)
order by c.location, trans_sum desc;

Essentially i want the top aggregated value of the transaction column for each type and location.

Location Type    Noun
--------------------- 
Atlanta  Channel 750 
Atlanta  Direct  2250 
Atlanta  CC      1850 
Chicago  Channel 625 
Chicago  Direct  1125 
Chicago  CC      612
Dallas   Channel 2183
Dallas   Direct  1165
Dallas   CC      965

.................................. The result i am looking for is: ................................

    Location Type    Noun
    ---------------------  
    Atlanta  Direct  2250 
    Chicago  Direct  1125
    Dallas   Channel 2183

...................................

So, a sql list return with the top location and type aggregated value. I'd also like the top 3 values for each location/type paring

Answer 1

remove c2.type in your select

select  
c.location, 
c.type,
sum(c.transaction) as trans_sum
from sales c
group by c.location, c.type
having trans_sum = (
select top 1
sum(c2.transaction) as trans_sum2
from sales c2
where c2.location = c.location
group by c2.location, c2.type
order by c2.location, trans_sum2 desc
)
order by c.location, trans_sum desc;

Answer 2

you probably use sql-server!? If so, I got a first error on using the name "transaction" as a column. This name is already used by the sql server, so I changed it.

Secondly, I don't think you can use the alias "trans_sum" in your having statement. Use sum(c.trans) instead.

This should work:

select  
       c.location, 
       c.type,
       sum(c.trans) as trans_sum
 from  foobar c
group by c.location, c.type
having sum(c.trans) = (
         select top 1
                sum(c2.trans) as trans_sum2
           from foobar c2
          where c2.location = c.location
          group by c2.location, c2.type
          order by c2.location, trans_sum2 desc
        )
order by c.location, trans_sum desc;