SQL distinct and count

sql count aggregate distinct
Ryan George picture Ryan George · May 27, 2009 · Viewed 54.3k times · Source

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

Tetraneutron picture Tetraneutron · May 27, 2009 
SELECT date, PhoneNumber, count(phone) AS count
    FROM calls
    GROUP BY date, PhoneNumber

should do it I think

Related questions

Performing a query on a result from another query?

I have a the query: SELECT availables.bookdate AS Date, DATEDIFF(now(),availables.updated_at) as Age FROM availables INNER JOIN rooms ON availables.room_id=rooms.id WHERE availables.bookdate BETWEEN '2009-06-25' AND date_add('2009…

Read More
How to use count and group by at the same select statement

I have an sql select query that has a group by. I want to count all the records after the group by statement. Is there a way for this directly from sql? For example, having a table with users I …

Read More
SQL query for finding records where count > 1

I have a table named PAYMENT. Within this table I have a user ID, an account number, a ZIP code and a date. I would like to find all records for all users that have more than one payment per …

Read More