CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Rouzbeh Zarandi
Rouzbeh Zarandi

Reputation: 1082

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(fact_date))
go

and

select count(*)
from tbl_fact
where stat = 1 and id = 16 and year(fact_date) in (select Distinct(year(fact_date)) from tbl_fact)
group by (year(fact_date))
go

i have records that taged with dates that for now i have dates from 2017 and 2018 so i need counts for each year. but the id=16 has only date tag of 2018 not 2017 so i get result as

eg: 15

how ever it should be like 

eg: 0 //2017
    15 //2018

Upvotes: 1

Views: 19606

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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(case when stat = 1 and id = 16 then 1 else 0 end) as cnt_16
from tbl_fact
group by year(fact_date)
order by yyyy;

Upvotes: 3

Mithrandir
Mithrandir

Reputation: 25337

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

select 
     count(*) as [COUNT], 
     year(fact_date) as [Year]
from tbl_fact
where stat = 1 and id = 16
group by (year(fact_date));

or as one string

select 
     count(*) + ' // ' + year(fact_date) as [grouping]
from tbl_fact
where stat = 1 and id = 16
group by (year(fact_date));

Upvotes: 2

M.Ali
M.Ali

Reputation: 69524

SELECT CAST([CountPerYear] AS VARCHAR(10)) 
        + ' // ' + 
        CAST([Year] AS VARCHAR(10))
FROM 
 (
    select   year(fact_date) [Year]
           , count(*)  [CountPerYear]
    from tbl_fact
    where stat = 1 and id = 16
    group by year(fact_date)
 )x

Upvotes: 0

Related Questions