Dynamically count values in column, grouped by months

Question

I'm trying to get a single mySQL query that returns the count of unique values, grouped by months.

I have a table created based on data similar to this:

CREATE TABLE `animals` (
  `timestamp` datetime NOT NULL,
  `animal` tinytext NOT NULL,
  `comment` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `animals` (`timestamp`, `animal`, `comment`) VALUES
('2019-06-03 09:09:00', 'dog', 'good'),
('2019-06-03 12:00:00', 'cat', ''),
('2019-06-03 19:00:00', 'cat', ''),
('2019-07-04 09:00:00', 'cat', ''),
('2019-07-04 12:00:00', 'cat', 'feisty'),
('2019-07-04 18:51:00', 'dog', ''),
('2019-08-05 09:00:00', 'cat', ''),
('2019-08-05 12:00:00', 'cat', ''),
('2019-08-05 19:00:00', 'cat', ''),
('2019-09-06 09:00:00', 'cat', ' evil'),
('2019-09-06 12:00:00', 'cat', ''),
('2019-09-06 19:00:00', 'cat', '')

I've managed to write a query that at least gives me the count per month (as long as it is more than zero), but the query just returns the count for "cat", "dog" or anything I explicitly ask for.

My goal is to get a response similar to the following:

month   | dog | cat
-------------------
2019-06 | 1   | 2
2019-07 | 1   | 2
2019-08 | 0   | 3
2019-09 | 0   | 3

How do I writhe such a query?

Is it possible to write a query that automatically counts any new values in the animal column too?

Thanks

score 1 · Accepted Answer · edited Nov 10 '19 at 01:50

1

You can use the following code, to get flexible columns from the animal column , that does the counting for you.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'Sum(`animal` =  ''',
      col,
      ''')  as `', 
      col, '`')
  ) INTO @sql
FROM
(
  select animal col
  from animals
)d;
SET @sql = CONCAT('SELECT date_format(`timestamp`, "%Y-%m") `month`, ', @sql, ' 
                  from `animals` 
                  group by `month`
                  order by `month`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Se dbfiddle example https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=09d0f26087d66452fde1a22e91de7b3a

edited Nov 10 '19 at 01:50

Nick

138,499
22
57
95

answered Nov 10 '19 at 01:34

nbk

45,398
8
30
47

Thank you! I realized I should have asked how to format it for PHP as well, currently trying to "translate" it. – Kristoffer Forsgren Nov 10 '19 at 11:57
this will be quite hared because if the many apostrophs, you could always do this in a stored procedure – nbk Nov 10 '19 at 14:01

GMB · Answer 2 · 2019-11-10T03:27:03.227

You want conditional aggregation:

select 
    date_format(`timestamp`, '%Y-%m') `month`,
    sum(`animal` = 'dog') dog,
    sum(`animal` = 'cat') cat
from `animals` 
group by `month`
order by `month`

Demo on DB Fiddle:

month   | dog | cat
:------ | --: | --:
2019-06 |   1 |   2
2019-07 |   1 |   2
2019-08 |   0 |   3
2019-09 |   0 |   3

If you want to handle dynamically the column list, then you have to go for dynamic sql:

set @sql = null;
select 
    concat(
        'select ',
        'date_format(`timestamp`, ''%Y-%m'') `month`, ',
        group_concat(
            concat(
                'sum(`animal` = ''', 
                `animal`, 
                ''') ', 
                `animal`
            )
            order by `animal`
            separator ', '
        ),
    ' from `animals` ',
    'group by `month` '
        'order by `month`'
    )
into @sql
from (
    select distinct `animal` from `animals`
) t;

select @sql;

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

OP already achieved this. He is asking for dynamically adding columns if other animals appear in the table. — Islingre, Nov 10 '19 at 00:57
@Islingre: correct. I updated my answer with a solution that uses dynamic sql. — GMB, Nov 10 '19 at 01:43
@GMB Thank you! I might end up using the first example, and making two querys in a PHP script unless I manage to translate the more dynamic query into a mysqli query for PHP :) — Kristoffer Forsgren, Nov 10 '19 at 11:59

score 0 · Answer 3 · answered Nov 10 '19 at 00:56

0

You can have

SELECT date_format(`timestamp`, '%Y-%m') AS month, animal, COUNT(*) as count
FROM animals
GROUP BY 1, 2

but this won't give you dynamically more columns. For more columns, I guess you need to build a dynamic SQL command looping over the distinct animals you have. If you really need this, you should consider to build that gives you the SQL string or directly the result.

answered Nov 10 '19 at 00:56

Islingre

2,030
6
18

I am not that confident ybout functions/procedures in MySQL, but perhaps this post might give you a clue for executing dynamic SQL statements: https://stackoverflow.com/questions/23178816/mysql-dynamic-query-in-stored-procedure A tutorial on loops can be found here: http://www.mysqltutorial.org/stored-procedures-loop.aspx – Islingre Nov 10 '19 at 01:00

Dynamically count values in column, grouped by months

3 Answers3