SQL distinct and count

Question

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?

Answer 1

That's because you're grouping by date not by phone.

In one grouping of a date there may be three different numbers and it's impossible for SQL to know which one you want.

Answer 2

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

should do it I think

Answer 3

You may want to try something like

SELECT Date, Phone, Count(*) As Count From Calls GROUP BY Date, Phone

This will give you a tally of each phone number on each date, with how many times that number appeared on that date.

Answer 4

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

(You don't need DISTINCT with a GROUP BY.)

Answer 5

SELECT Date, Phone, Count(Phone) As Count From Calls GROUP BY Date, Phone

this query gets me a list of all the records, and I get a duplicates of the phone numbers and dates with a count of how often the phone number shows up for that date. How can I make it so that I get a distinct set for phone numbers and dates with the count of how many times that number shows up for that date?