Applying Max on Sum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Applying Max on Sum

Author

Topic

itzikgili
Starting Member

6 Posts

Posted - 2012-09-08 : 07:09:10

as if i have the table of orders

user | deal
-------|------------
user1 | 450
user2 | 440
user3 | 460
user1 | 200
user2 | 200
user1 | 200

want the query to sum all the deals of each user and return the user
who paid the most

like for the table above it will return
"user1"

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-08 : 07:16:10

[code]
select top(1) user
from orders
group by user
order by sum(deal) desc
[/code]

itzikgili
Starting Member

6 Posts

Posted - 2012-09-08 : 07:22:12

is there another option using subquery?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-08 : 07:27:34

You possible can, but each of them more complex and unnecessary, at least in this case - bitsmed's solution is the best. Is your actual problem more involved that forces you to make use of a subquery?

SELECT TOP (1) * FROM 
(SELECT USER, SUM(deal) AS DealTotal FROM Orders GROUP BY USER) s
ORDER BY DealTotal DESC

itzikgili
Starting Member

6 Posts

Posted - 2012-09-08 : 07:30:56

actualy yes, i just gave an example

i thought you can make like a virtual table (subquery)
then applying the max.
but i was not able to bring back the user,
just the value of the sum :(

and thanks for the help till now

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-08 : 07:37:01

I am afraid I am not following. If you add the sum column to bitsmed's query wouldn't you get what you are looking for?

select top(1) user, sum(deal) as DealTotal
  from orders
 group by user
 order by sum(deal) desc

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-08 : 07:41:30

Well, you could use the sql I wrote, as a subselect in a where statement, like this:


select *
  from orders
 where user in (select top(1) user
                  from orders
                 group by user
                 order by sum(deal) desc
               )

Does this help you?

I'm not quite sure, that exactly you want to accomplish.

itzikgili
Starting Member

6 Posts

Posted - 2012-09-08 : 07:42:45

i'm sorry if i wasnt clear.
i need the subquery solution.

i just simplifyed the problame over here.
i want the solution for this in subqueries so i can use it for the more complexed problame

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-08 : 07:50:48

or you could use the subselect to join table:


select *
  from orders
       inner join (select top(1) user
                     from orders
                    group by user
                    order by sum(deal) desc
                  ) as topuser
               on topuser.user=orders.user

itzikgili
Starting Member

6 Posts

Posted - 2012-09-08 : 08:01:44

i just dont want to use the order and top (1)
i need to use MAX

itzikgili
Starting Member

6 Posts

Posted - 2012-09-08 : 08:13:59

i have tried

select pid, quan
from ( select od.ProductID pid, sum(od.Quantity) quan
from [Order Details] od
group by od.ProductID) as tabletemp, Products p
where p.ProductID = tabletemp.pid
and tabletemp.quan = MAX(select sum(od.Quantity)
from [Order Details] od
group by od.ProductID))

the result should be the product that has the most sales
its like max(sum(od.quantity))
which cant be used like that

so i did it with subquery and still it wont work!

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-08 : 08:35:18

now, this is not going to be pretty, but here goes:


select od.productid as pid
      ,od.quantity as quan
  from [order details] as od
       inner join (select productid
                         ,sum(quantity) as quantity
                     from [orders details]
                    group by productid
                  ) as order_detail_sum
               on order_detail_sum.productid=od.productid
 where order_details.quantity=(select max(quantity) as quantity
                                 from (select productid
                                             ,sum(quantity) as quantity
                                         from [product details]
                                        group by productid
                                      ) as order_detail_sum
                              )

Hope this helps

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources