CODEWITHSUNDEEP
sqlsql-server
Steam
Steam

Reputation: 9856

COUNT with DISTINCT

I store all the shipping info for a customer in MyTable - CustomerId, ShippingID, ItemID, ShipDate. A shipment/shippingID can contain one or more Items/ItemIDs. A shipping id can have only one shipdate. Here is a sample of the table - 

CustomerID, ShippingID, ItemID, ShipDate
C1, A1, I200, today
C1, A1, I88, today
C1, A2, I7, tomorrow
C1, B1, I955, yesterday
C2, B2.....et cetra

For a customerID, I want to display the distinct shippingIDs, number of items in a shippingID, ShipDate.

Expected output - 

C1, A1, 2, today
C1, A2, 1, tomorrow
C1, B1, 1, yesterday
...etc

I tried - 

select distinct shippingid, 
count(*) over() itemid, 
orderdate
from mytable
where customerID = 'C1'

Output - 

C1, A1, 4, today
C1, A2, 4, tomorrow

Problem is that it counts all the items for C1. I want to count only the items in a shippingID of a customer. How do I do this ?

EDIT - Right now, I don't need it for all customers. Just for one. So group by is not necessary.

Thanks.

Upvotes: 1

Views: 1050

Answers (3)

Dave Zych
Dave Zych

Reputation: 21897

Why not use a Group By? It's the easiest way... SqlFiddle

SELECT
    CustomerID,
    ShippingID,
    count(1)
FROM mytable
GROUP BY CustomerID, ShippingID

Upvotes: 1

Lamak
Lamak

Reputation: 70668

SELECT  shippingid,
        COUNT(*) Items,
        ShipDate 
FROM mytable
WHERE customerID = 'C1'
GROUP BY shippingid,
         ShipDate

Upvotes: 2

mart
mart

Reputation: 224

Maybe this: 

select customerID, shippingid, itemid, count(1)
from mytable
group by customerId, shippingid, itemid

Upvotes: 0

Related Questions