How to count the number of a value in each month and year? SQL-CodePudding

how can I count the number of 'cancel' value in each month and year? Thank you!

 res                   date
---------------------------------------   
cancel                 2022-05-02
cancel                 2022-04-22
cancel                 2022-04-08
cancel                 2022-03-01

MS SQL

expected output:

 month&year          numcases
-------------------------------
05-2022                  300
04-2022                  543

ETC....

CodePudding user response：

You can start trying something like this:

SELECT YEAR([date]), MONTH([date]), COUNT(*) AS [Number]
FROM [Table]
GROUP BY YEAR([date]), MONTH([date])

The column [date] should be datetime. If it's text you will have to cast it:

SELECT YEAR(CAST([date] AS datetime)), ...