Group mysql results

Question

Good evening, I need to make a query in the table below:

But bring me the result of how many type = E , type = S grouped by month, this result I will play in a chart that will display by month how many input and output;

Follow code to create table :

CREATE TABLE `movements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_product` int(11) NOT NULL,
  `id_user` int(11) NOT NULL,
  `type` enum('S','E') NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `movements_product_idx` (`id_product`),
  KEY `movements_user_idx` (`id_user`),
  CONSTRAINT `movements_product` FOREIGN KEY (`id_product`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `movements_user` FOREIGN KEY (`id_user`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

I tried this code, it’s grouping by month but I don’t know how many type=’S' or 'E' I had this month;

select count(id)Total, DATE_FORMAT (`date`,'%b') AS `mes` from movements group by month(date);

If anyone knows how you do...

Answer 1

If I understand correctly, I believe:

SELECT DATE_FORMAT(`date`,'%b') AS `mes`, 
       SUM(CASE WHEN Type = 'E' THEN 1 ELSE 0 END) AS TypeE, 
       SUM(CASE WHEN Type = 'S' THEN 1 ELSE 0 END) AS TypeS 
FROM movements 
GROUP BY month(date);

can serve you.