SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Applying Max on Sum
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

itzikgili
Starting Member

6 Posts

Posted - 09/08/2012 :  07:09:10  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

344 Posts

Posted - 09/08/2012 :  07:16:10  Show Profile  Reply with Quote

select top(1) user
  from orders
 group by user
 order by sum(deal) desc
Go to Top of Page

itzikgili
Starting Member

6 Posts

Posted - 09/08/2012 :  07:22:12  Show Profile  Reply with Quote
is there another option using subquery?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/08/2012 :  07:27:34  Show Profile  Reply with Quote
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

Edited by - sunitabeck on 09/08/2012 07:28:58
Go to Top of Page

itzikgili
Starting Member

6 Posts

Posted - 09/08/2012 :  07:30:56  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/08/2012 :  07:37:01  Show Profile  Reply with Quote
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
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

344 Posts

Posted - 09/08/2012 :  07:41:30  Show Profile  Reply with Quote
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.
Go to Top of Page

itzikgili
Starting Member

6 Posts

Posted - 09/08/2012 :  07:42:45  Show Profile  Reply with Quote
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
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

344 Posts

Posted - 09/08/2012 :  07:50:48  Show Profile  Reply with Quote
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

Edited by - bitsmed on 09/08/2012 07:51:39
Go to Top of Page

itzikgili
Starting Member

6 Posts

Posted - 09/08/2012 :  08:01:44  Show Profile  Reply with Quote
i just dont want to use the order and top (1)
i need to use MAX
Go to Top of Page

itzikgili
Starting Member

6 Posts

Posted - 09/08/2012 :  08:13:59  Show Profile  Reply with Quote
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!
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

344 Posts

Posted - 09/08/2012 :  08:35:18  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000