Count rows in a table based on a DATE field

Question

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.

Answer 1

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.

Answer 2

Let us call your table ORDERS. As mentioned in your question, for a certain day there might not be any orders but in your final report, even those days should also be present.

To get the desired output we must combine this, LEFT JOIN, GROUP BY and COUNT.

Step 1 : Create a view called V_DATES :

create view V_DATES
as
select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date 
from (
        select 0 i union select 1 union select 2 union select 3 
        union select 4 union select 5 union select 6 union select 7 
        union select 8 union select 9
) t0,
(
        select 0 i union select 1 union select 2 union select 3 
        union select 4 union select 5 union select 6 union select 7 
        union select 8 union select 9
) t1,
(
        select 0 i union select 1 union select 2 union select 3 
        union select 4 union select 5 union select 6 union select 7 
        union select 8 union select 9
) t2,
(
        select 0 i union select 1 union select 2 union select 3 
        union select 4 union select 5 union select 6 union select 7 
        union select 8 union select 9
) t3,
(
        select 0 i union select 1 union select 2 union select 3 
        union select 4 union select 5 union select 6 union select 7 
        union select 8 union select 9
) t4

Step 2: Execute the following query to get your desired output

select vd.selected_date, count(distinct c.client_id)
from V_DATES vd
left outer join CLIENTS c
on vd.selected_date = c.date_added
where vd.selected_date between '2019-03-01' and '2019-03-06'
group by vd.selected_date;