Filter Records by Month/Year Codeigniter

Question

I have the following SQL structure:

CREATE TABLE IF NOT EXISTS `noticia` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_categoria` int(11) DEFAULT NULL,
  `titulo` varchar(255) NOT NULL,
  `previa` text NOT NULL,
  `descricao` text,
  `imagem` varchar(255) NOT NULL DEFAULT '',
  `data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `views` int(11) DEFAULT '0',
  `youtube` varchar(255) DEFAULT NULL,
  `ativo` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`,`ativo`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I would like to elaborate an SQL that query the records, grouping month and year. that appears as follows:

1. How can I group MONTH/YEAR to assemble this listing? To display, I develop normal, my doubt is only in SQL.

I thought I’d make:

SELECT * FROM noticia GROUP BY MONTH(data);

If I do so, I will group only by month, and if I have the same month next year, I will group as well. And that is not the intention. I thank you!

Answer 1

I was able to find a solution. If anyone needs it, it’s there.

SELECT 
    EXTRACT(MONTH FROM data) AS mes, 
    EXTRACT(YEAR FROM data) AS ano, 
    COUNT(id) AS total_noticia, 
    (CASE month(data) 
        when 1 then 'Janeiro'
        when 2 then 'Fevereiro'
        when 3 then 'Março'
        when 4 then 'Abril'
        when 5 then 'Maio'
        when 6 then 'Junho'
        when 7 then 'Julho'
        when 8 then 'Agosto'
        when 9 then 'Setembro'
        when 10 then 'Outubro'
        when 11 then 'Novembro'
        when 12 then 'Dezembro'
    END) AS mes_extenso
FROM 
    noticia 
GROUP BY 
    mes