MySQL - Count and GroupBy

Question

I have this structure:

id|  date_1  |  date_2
---------------------
01|2017-01-01|2017-02-22
02|2017-01-02|2017-03-25
03|2017-02-10|2017-03-20
04|2017-03-11|2017-04-10
05|2017-03-15|2017-05-01
06|2017-03-20|2017-05-20

I would need this kind of result:

Month  |Count(date_1)|Count(date_2)
---------------------------------
2017-01| 2           | 0
2017-02| 1           | 1
2017-03| 3           | 2
2017-04| 0           | 1
2017-05| 0           | 2

Now, I use this query (it works with only one date):

SELECT CONCAT(YEAR(date_1), '-', DATE_FORMAT(date_1,'%m')) AS month,
COUNT(*) AS items
FROM table
GROUP BY YEAR(date_1), MONTH(date_1)
ORDER BY date_1 DESC

Possible duplicate of [Multiple Self-Join based on GROUP BY results](https://stackoverflow.com/questions/47758492/multiple-self-join-based-on-group-by-results) — philipxy, Dec 17 '17 at 22:13
You want a table of all distinct months left joined on Month with 2 subselects like your query. Although that can be expressed other ways. You can left join all distinct months with each subselect then join those results on month. You can express either of those with asymmetrical nesting. The main idea is adding different aggregated subquery columns via a common key. You would have found many extant duplicate Qs & As if, as you always should, you had googled many clear, concise, specific phrasings of (parts & all of) your question/problem/goal. — philipxy, Dec 17 '17 at 22:31

score 1 · Answer 1 · answered Dec 17 '17 at 16:33

1

You could union all the date values and then group and count them:

SELECT   DATE_FORMAT(d, '%y-%m'), COUNT(*)
FROM     (SELECT date_1 AS d FROM mytable 
          UNION ALL
          SELECT date_2 FROM mytable) t
GROUP BY DATE_FORMAT(d, '%y-%m')
ORDER BY d DESC

answered Dec 17 '17 at 16:33

Mureinik

297,002
52
306
350

1

not quite right: http://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=fd171ca7979a88ff2cc7a8cbd2f82155 – Radim Bača Dec 17 '17 at 19:47
1

This output table is not as requested. – philipxy Dec 17 '17 at 22:20

score 1 · Answer 2 · answered Dec 17 '17 at 17:10

To get the count of date_1 and date_2 in two different fields, with sub query:

    SELECT  DATE_FORMAT(temp1.d, '%y-%m'), COALESCE(d1count,0) AS date_1_count, COALESCE(d2count,0)AS date_2_count
    FROM (
        select date_1 as d  from dates  group by date_1 
        union all 
        select date_2 as d from dates group by date_2
        ) as temp1

    LEFT JOIN (
         select date_1, count(*) as d1count 
         from dates 
         group by DATE_FORMAT(date_1, '%y-%m')) as temp2 
    on DATE_FORMAT(temp2.date_1, '%y-%m') = DATE_FORMAT(temp1.d, '%y-%m')

    LEFT JOIN (
         select date_2, count(*) as d2count 
         from dates 
         group by DATE_FORMAT(date_2, '%y-%m')) as temp3
    on DATE_FORMAT(temp3.date_2, '%y-%m') = DATE_FORMAT(temp1.d, '%y-%m')

    GROUP BY  DATE_FORMAT(temp1.d, '%y-%m')

score 1 · Answer 3 · answered Dec 17 '17 at 20:03

Consider using subqueries behind SELECT

SELECT   distinct DATE_FORMAT(t.d, '%y-%m'),
     (
        SELECT count(*) 
        FROM your_table as dd 
        where DATE_FORMAT(dd.date_1, '%y-%m') = DATE_FORMAT(t.d, '%y-%m')
     ) as count_date_1,
     (
        SELECT count(*) 
        FROM your_table as dd 
        WHERE DATE_FORMAT(dd.date_2, '%y-%m') = DATE_FORMAT(t.d, '%y-%m')
     ) as count_date_2
FROM     
(
  SELECT date_1 AS d FROM your_table 
      UNION ALL
  SELECT date_2 as d FROM your_table
) as t

dbfiddle demo

MySQL - Count and GroupBy

3 Answers3

Linked