Mysql – Count rows in a table based on a DATE field

MySQLmysql-5.5querysubquery

I have a table with 3 columns (order_id, client_id, date_added).
The content would look something like this:

order_id | client_id  | date_added
-----------------------------------
14152    | NA4156     | 2019-03-01
14153    | EA4656     | 2019-03-02
14154    | EA4656     | 2019-03-02
14155    | CA4456     | 2019-03-03
14156    | DA4556     | 2019-03-03
14157    | EA4656     | 2019-03-03
14158    | FA4756     | 2019-03-06
14159    | GA4856     | 2019-03-06

and so on.
As you can see on a certain day there might be no entries.

I am trying to obtain the following result:

date       | no_of_rows
-----------------------------
2019-03-01 | 1
2019-03-02 | 2
2019-03-03 | 4
2019-03-04 | 4
2019-03-05 | 4
2019-03-06 | 6

I understood from here how to generate all dates that I am looking for, but I am not sure now how to count the unique clients based on client_id for each date.

At the moment I am doing this step by step and moving the data into an Excel and processing it from there using the query below:

get unique number of clients registered until and including 2019-03-01

SELECT COUNT(client_id) FROM clients WHERE date_added < '2019-03-02' GROUP BY client_id

get unique number of clients registered until and including 2019-03-02

SELECT COUNT(client_id) FROM clients WHERE date_added < '2019-03-03' GROUP BY client_id

and so on.

But this method seems to be a little bit exhaustive and I am pretty sure there is a way to do it in a single query, but not sure where to start from.

Best Answer

You have to utilise the COUNT function along with the GROUP BY function along the lines of

SELECT
 date_added AS Date,
 COUNT(Client_ID ) AS no_of_rows 
FROM CLIENTS
GROUP BY date_added  
ORDER BY date_added;

Using the above produces output in the form of 

Date        no_of_rows
2019-03-01  1
2019-03-02  2
2019-03-03  3
2019-03-06  2

As an aside it is generally a good idea not to name a column with the name of a datatype i.e I would probably call the Data column in the result ClientDate.

Related Question