How to count the number of times that certain values ​​appear in a table in SQL and return that number in a column?

advertisements

I've used the COUNT function to determine how many rows there are in a table or how often a value appears in a table.

However, I want to return the 'count' for multiple values in a table as a seperate column.

Say we a have a customer table with columns; Customer ID #, Name, Phone Number.
Say we also have a sales table with columns: Customer ID, Item Purchased, Date

I would like my query to return a column for customer ID and a column for # of times that customer ID appeared in the sales table. I would like to do this for all of my customer IDs at once--any tips?


You can use group by:

select   customer_id,
         count(*)
from     sales
group by customer_id

This will return a row by customer ID with the count of how many matching items.