CODEWITHSUNDEEP
sqldb2
nate_weldon
nate_weldon

Reputation: 2349

IBM DB2 looking to get a unique count of each duplicate item in a table and list it with another value of the table

i have a DB2 table (orderitems) that has columns named ORDERITEMS_ID and ORDERS_ID I'm looking to get a count of all of the orderitems_id that are associated with each orders_id. I can get the count but i would like the order_id associated with that count.

i've tried 

SELECT COUNT(orderitems_id) as total 
FROM orderitems 
GROUP BY orders_id 
ORDER BY total DESC

i believe this is giving me the total count of each of the items in a order_id. but i'm not sure how to add the order_id with the result set

if i try the following 

SELECT orders_id, COUNT(orderitems_id) as total 
FROM orderitems 
GROUP BY orders_id 
ORDER BY total DESC

this is a bad query

i've looked into joining but that seems to be dealing with two tables...not sure how to append this information.

Upvotes: 0

Views: 8094

Answers (1)

Alexis Dufrenoy
Alexis Dufrenoy

Reputation: 11966

Try this:

select distinct orders_id, 
       count(orderitems_id) as total 
from orderitems 
group by orders_id 
order by total desc

Summarizing precisely what you want to do often helps. In this case, you want a count of orderitems_id for each distinct orders_id, e.g. for each different value of orders_id and not for each line. When you want a result depending on the different values of a column, think distinct.

Upvotes: 1

Related Questions