CODEWITHSUNDEEP
mysql
Kristoffer Forsgren
Kristoffer Forsgren

Reputation: 13

Dynamically count values in column, grouped by months

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

Upvotes: 1

Views: 730

Answers (3)

GMB
GMB

Reputation: 222482

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;

Upvotes: 0

nbk
nbk

Reputation: 49375

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

Upvotes: 1

Islingre
Islingre

Reputation: 2349

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.

Upvotes: 0

Related Questions