0

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.

  • Specify your MySQL version. how to count the unique clients based on client_id for each date. SELECT COUNT(DISTINCT client_id) – Akina Mar 20 '19 at 12:16
  • @Akinathe Mysql Version is 5.5.50 – Adrian George Mar 20 '19 at 12:35
  • SELECT t1.date, COUNT(DISTINCT t2.client_id) FROM (subquery which generates dates list) t1, clients t2 WHERE t1.date >= t2.date GROUP BY t1.date – Akina Mar 20 '19 at 12:38
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.

| improve this answer | |
0

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.