how can I count the number of 'cancel' value in each month and year? Thank you! MS SQL expected output: ETC.... Answer You can start trying something like this: The column [date] should be datetime. If it's text you will have to cast it:

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

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

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

MS SQL

expected output:

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

 
 month&year          numcases-------------------------------05-2022                  30004-2022                  543​

ETC….

Answer

You can start trying something like this:

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

 
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)), ...

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

Advertisement

Answer