MySQL: multiple count in one row

Question

I have next data:

mysql> select no,crt_date,tobilling_date,sent_to_client,dop_prov from assistfin limit 20;
+--------+---------------------+---------------------+----------------+------------+
| no     | crt_date            | tobilling_date      | sent_to_client | dop_prov   |
+--------+---------------------+---------------------+----------------+------------+
|  50.01 | 2014-02-05 10:28:10 | 2014-02-05 14:42:35 | 2014-04-16     | 2014-09-23 |
| 123.01 | 2014-02-05 19:17:36 | 2014-03-17 18:58:05 | 2014-04-10     | 2014-06-30 |
|  51.01 | 2014-02-06 00:09:32 | 2014-03-20 16:53:46 | 2014-04-10     | 2014-06-30 |
| 124.01 | 2014-02-06 15:29:08 | 2014-03-20 17:04:42 | 2014-04-10     | 2014-06-30 |
| 230.01 | 2014-02-07 22:01:11 | 2014-03-20 16:41:03 | 2014-04-10     | 2014-06-30 |
| 252.01 | 2014-02-08 02:52:33 | 2014-03-20 16:43:03 | 2014-04-10     | 2014-06-30 |
| 123.02 | 2014-02-08 03:00:52 | 2014-03-17 18:58:10 | 2014-04-10     | 2014-06-30 |
| 213.01 | 2014-02-08 04:01:35 | 2014-03-26 19:03:01 | 2014-04-10     | 2014-09-19 |
|  55.01 | 2014-02-08 21:04:45 | 2014-03-07 18:40:46 | NULL           | 2014-06-26 |
| 126.01 | 2014-02-08 21:46:58 | 2014-09-02 18:39:36 | 2014-09-09     | 2014-09-26 |
| 284.01 | 2014-02-09 01:52:54 | 2014-06-11 19:11:06 | 2014-07-02     | 2014-07-21 |
| 261.01 | 2014-02-09 02:20:34 | 2014-03-17 20:57:39 | 2014-04-10     | 2014-06-30 |
| 318.01 | 2014-02-09 03:09:28 | 2014-03-17 20:44:25 | 2014-04-10     | 2014-06-30 |
| 225.01 | 2015-02-10 03:21:08 | 2014-03-20 16:57:56 | 2014-04-10     | 2014-06-30 |
| 248.01 | 2014-02-09 03:30:58 | 2014-03-18 18:02:21 | 2014-04-10     | 2014-06-30 |
| 178.01 | 2014-04-05 03:35:25 | 2014-03-21 17:10:12 | 2014-04-10     | 2014-06-30 |
| 184.01 | 2014-04-08 04:01:13 | 2015-03-20 16:38:02 | 2015-04-10     | 2015-06-30 |
| 320.01 | 2014-04-08 05:57:23 | 2015-03-17 20:49:19 | 2015-04-10     | 2015-06-30 |
| 230.02 | 2015-05-08 06:18:15 | 2016-03-20 16:41:08 | 2016-04-10     | 2016-06-06 |
| 325.01 | 2014-05-09 06:23:50 | 2015-03-17 20:42:04 | 2015-04-10     | 2015-06-30 |
+--------+---------------------+---------------------+----------------+------------+

Need to get next data:

+---------+---------+--------+-----------+---------+
| year    | Created | Passed | To client | To prov |
+---------+---------+--------+-----------+---------+
| 2016-01 |    1901 |   1879 |      1873 |    1743 |
| 2016-02 |    2192 |   2169 |      2114 |    1912 |
| 2016-03 |    2693 |   2639 |      2539 |    2309 |
| 2016-04 |    2634 |   2574 |      2273 |    1976 |
| 2016-05 |    2593 |   2497 |      1109 |     949 |
| 2016-06 |     471 |    449 |         2 |      78 |
+---------+---------+--------+-----------+---------+

Where year like DATE_FORMAT(curdate(), '%Y-%m'), next column Count(assistfin.crt_date) as Created.

The problem is that crt_date can be like 2015%, but sent_to_client or dop_prov can be like 2016%.

How to make correct query?

Just a quick question, I think I know what you want and it would be solved using unions. But just quickly the above query you posted for the second entry in your data (no=123.01) would create a 1 in the Created column for '2014-02' and since tobilling_date is not null, it will also place a 1 in the passed column in the same month. But you actually want that 1 to be in the March row, thus it is possible that passed > created? — brett, Jun 06 '16 at 10:07
Yes, everything is correct. I need to use the date as not in the table field but as a separate list. — Vadim Nosyrev, Jun 06 '16 at 10:25

score 1 · Answer 1 · edited May 23 '17 at 12:15

Ok sorry this is so long and messy and also I couldnt do it using unions as I so arrogantly posted in the comments, also have to reference MySQL: Is it possible to 'fill' a SELECT with values without a table? that gave me the list of months. You could rewrite it so you left join all the tables to crt_date, but then it wont show a month when nothing was created, hence the generated months table. The original query had a limit 120 in the months, but I have replaced it with a datetime > '2014' for you to change with your earliest date.

Try this and see how quickly it runs for you.

select Months.yearmonth, created, passed, to_client, to_prov

from
(SELECT date_format(datetime,'%Y-%m') as yearmonth
FROM (
    select (curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) MONTH) as datetime
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) AS t
where datetime > '2014' -- enter your earliest year here
ORDER BY datetime ASC) Months left join
(select date_format(crt_date,'%Y-%m') as yearmonth, count(no) as "created" from assistfin group by yearmonth) created on Months.yearmonth=created.yearmonth
left join
(select date_format(tobilling_date,'%Y-%m') as yearmonth, count(no) as "passed" from assistfin group by yearmonth) passed on Months.yearmonth=passed.yearmonth
left join
(select date_format(sent_to_client,'%Y-%m') as yearmonth, count(no) as "to_client" from assistfin group by yearmonth) to_client on Months.yearmonth=to_client.yearmonth
left join
(select date_format(dop_prov,'%Y-%m') as yearmonth, count(no) as "to_prov" from assistfin group by yearmonth) to_prov on Months.yearmonth=to_prov.yearmonth
where 
group by yearmonth;

Try running each subquery in the from statement and hopefully that shows you how it is done. But basically it works by joining the subqueries to the full list of months since xxxx-xx. Since if you just join them together, you could have issues with missing months on your base from table not generating despite other columns containing information for those periods. — brett, Jun 06 '16 at 12:51

ScaisEdge · Answer 2 · 2016-06-06T09:58:46.357

0

Use group by and date_forma in where

select  date_format(crt_date, '%Y-%m') as year, count(sent_to_client ), count(dop_pprov) 
from assistfin 
where date_format(crt_date, '%Y-%m') = date_format(now(), '%Y-%m')
group by year

for the year you can

select  date_format(crt_date, '%Y-%m') as year, count(sent_to_client ), count(dop_pprov) 
from assistfin 
where date_format(crt_date, '%Y') = date_format(now(), '%Y')
group by year

OR for A Range OF yearS you can

select  date_format(crt_date, '%Y-%m') as year, count(sent_to_client ), count(dop_pprov) 
from assistfin 
where date_format(crt_date, '%Y') 
           BETWEEN(date_format(now(),'%Y')-2) and date_format(now(), '%Y') 
group by year

edited Jun 06 '16 at 09:58

answered Jun 06 '16 at 08:24

ScaisEdge

131,976
10
91
107

which period of time do you need ? – ScaisEdge Jun 06 '16 at 08:34
I have update the answer for the year .. if you need another range fo time let me know – ScaisEdge Jun 06 '16 at 08:35
Thx for answer, period - year. Forexample row with crt_date=2015-02-09 and sent_to_client=2015-02-30, does not fall into the resulting table, becose where date_format(crt_date, '%Y') = date_format(now(), '%Y') – Vadim Nosyrev Jun 06 '16 at 09:51
sorry, need `and sent_to_client=2016-02-30,` – Vadim Nosyrev Jun 06 '16 at 09:59
I have update the asnwer for a range o year .. or you can set the date you prefer .. then seem the last questions in comment are not related to the OQ and is fair in this case mark teh answer as accepted and/or useful and post the new question in a new properly formated question .. – ScaisEdge Jun 06 '16 at 10:02

MySQL: multiple count in one row

2 Answers2

Linked