CODEWITHSUNDEEP
sqlsql-server
Jake12342134
Jake12342134

Reputation: 1767

Distinct counting a number of different columns in SQL Server

I have a table containing order data as follows (simplified):

ID    CustomerID    OrderId
1     1             100
2     1             101
3     1             102
4     2             103
5     3             104
6     2             105

I want to write a query that collates all of the unique customerIds present in this table and counts the amount of orders that each customer has. But I'm struggling with how to go about this.

Select distinct customerId from table

The above will give me all of the unique customerIds in the table, but then I'm not sure how to aggregate the counts of each customer's orders onto this data. Am I right in thinking it involves some kind of subquery?

Upvotes: 0

Views: 67

Answers (3)

apomene
apomene

Reputation: 14389

You need COUNT and GROUP BY. Try like:

SELECT CustomerID,COUNT(*) FROM myTable GROUP BY customerID

Upvotes: 4

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Simple aggregation with group by clause :

select customerId, count(*) as orders 
from table 
group by customerId;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You are describing group by:

Select customerId, count(*)
from table 
group by customerId;

This is a quite basic part of SQL syntax. You may want to brush up on your SQL.

Upvotes: 3

Related Questions