db2 query

[oriole72]

hi, i have a table of sales orders, with an
id - customer id number
quantity - the quantity of products bought
and another table that has a fname and lname and id

i want to find which customer bought the most of a product and only display that persons first name, last name and the number of products he bought
since my table can have multiple ids and products ordered, first i have to sum them, so i did this...

SELECT id, sum(quantity) FROM sales_order_items
GROUP BY id

and this at least sums each individual customers total products bought, but i dont know how to take the MAX of this!
And once i find the max, i'll need to grab the fname of the user from the customer table.

Any ideas how to get the MAX of my search and also the customers first name?

 

[Dagon]

Sounds like you just need to join to the customer table:

SELECT s.id, sum(s.quantity), c.firstname
FROM sales_order_items s, customer c
where c.id = s.id
GROUP BY id

 

[Dagon]

Could do:

SELECT s.id, sum(s.quantity)
FROM sales_order_items s, customer c
where c.id = s.id
GROUP BY s.id
order by sum(s.quantity) desc
fetch first 1 row only

Otherwise, it gets a bit messy:

SELECT s.id, sum(s.quantity)
FROM sales_order_items s, customer c
where c.id = s.id
GROUP BY s.id
having sum(s.quantity) =
(select max(sum_quantity)
from
(SELECT id, sum(quantity) sum_quantity
FROM sales_order_items
GROUP BY id))