Query To Select Count Of Records For Each Year

sorry for this newbie question ... im trying to get number of rows per year of my table..... this is my try: select count(*) from tbl_fact where stat = 1 and id = 16 group by (year

Solution 1:

A simple method to get all years in the data -- even when they don't meet the conditions of the where clause -- is to use conditional aggregation:

select year(fact_date) as yyyy,
       sum(casewhen stat = 1and id = 16then1else0end) as cnt_16
from tbl_fact
groupby year(fact_date)
orderby yyyy;

Solution 2:

You can get the count and year in two columns by:

selectcount(*) as [COUNT], 
     year(fact_date) as [Year]
from tbl_fact
where stat = 1and id = 16groupby (year(fact_date));

or as one string

selectcount(*) +' // '+year(fact_date) as [grouping]
from tbl_fact
where stat =1and id =16groupby (year(fact_date));

Solution 3:

SELECTCAST([CountPerYear] ASVARCHAR(10)) 
        +' // '+CAST([Year] ASVARCHAR(10))
FROM 
 (
    selectyear(fact_date) [Year]
           , count(*)  [CountPerYear]
    from tbl_fact
    where stat =1and id =16groupbyyear(fact_date)
 )x