CODEWITHSUNDEEP
mysqlsqlselect
Marco Di Perna
Marco Di Perna

Reputation: 11

MySQL - Count and GroupBy

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

Upvotes: 1

Views: 69

Answers (3)

Radim Bača
Radim Bača

Reputation: 10701

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

Upvotes: 1

tyro
tyro

Reputation: 1428

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')

Upvotes: 1

Mureinik
Mureinik

Reputation: 311188

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

Upvotes: 1

Related Questions