Search code examples
mysqlselectcountdistincthaving

MySQL select count only new id's for each year

I have a MySQL table that looks like this

    id   |   client_id   |    date
  --------------------------------------
     1   |       12      | 02/02/2008
     2   |       15      | 12/06/2008
     3   |       23      | 11/12/2008
     4   |       12      | 18/01/2009
     5   |       12      | 03/03/2009
     6   |       18      | 02/07/2009
     7   |       23      | 08/09/2010
     8   |       18      | 02/10/2010
     9   |       21      | 30/11/2010

What I am trying to do is get the number of new clients for each year. 2008 has 3 new clients(12,15,23), 2009 has 1 new client(18) and 2010 has 1 new client(21).

So far I have this query that gives me the distinct clients for each year, that is 3 for 2008, 2 for 2009 and 3 for 2010.

 SELECT COUNT(DISTINCT client_id) FROM table GROUP BY YEAR(date)

Any help would be appreciated..

Solution

  • You could use a subquery to get the first year of every client_id grouped by client_id, and then count the occurrence of client_id grouped by year, so: 

    SELECT COUNT(client_id), YEAR_MIN FROM (
    SELECT client_id, MIN(YEAR(date)) AS YEAR_MIN 
    FROM   table 
    GROUP BY client_id) AS T
GROUP BY YEAR_MIN

    SQL Fiddle here