SQL distinct and count

aggregatecountdistinctsql

I have a query where I want to get distinct dates, the phone numbers associated with those dates, and a count of the phone numbers per date.

For example, I have a database with dates and phone numbers and I want the result to be

9/2005      5554446666    3
9/2005      4445556666    1
10/2005     1112223333    1
11/2005     2223334444    2

I can get the dates and the counts with this query:

SELECT DISTINCT date, count(phone) AS count
FROM calls
GROUP BY date

What I can't seem to get is the phone number the 'count' is counting. I think I need some sort of aggregate function to get a single instance of the list of unique values but First() and a few others only throw a SQL error. Do I need a subquery?

Best Solution

SELECT date, PhoneNumber, count(phone) AS count
    FROM calls
    GROUP BY date, PhoneNumber 

should do it I think