count column and group by year, but missing Years with null

Question

count column and group by year, but missing Years with null

orenk

Old Hand

Points: 388
More actions
December 14, 2015 at 12:44 am

#307010

Hey,
what I like to do is count a column and group by year. Very easy :-),
select count(*) as Anzahl, year(Datum) as Jahr from KdTickets
where Typ = 1 and infonr = @InfoNr
group by year(datum)
But in the resault I miss some years, with no count.
Anzahl Jahr
==== ====
1 2012
1 2014
How can ich get the years 2013 and 2015 with '0'?
regards
Olaf

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

orenk (12/14/2015)
Hey,
what I like to do is count a column and group by year. Very easy :-),
select count(*) as Anzahl, year(Datum) as Jahr from KdTickets
where Typ = 1 and infonr = @InfoNr
group by year(datum)
But in the result I miss some years, with no count.
Anzahl Jahr
==== ====
1 2012
1 2014
How can ich get the years 2013 and 2015 with '0'?
regards
Olaf

If you had a table of Years, then you could outer join it to your results.

SELECT Years.Year as Jahr, COUNT(*) AS Anzahl

FROM Years LEFT JOIN KdTickets ON Years.Year = YEAR(KdTickets.Datum)

WHERE Typ = 1 AND infonr = @InfoNr

GROUP BY Years.Year;

The LEFT join will force all records from the Years table to be in the result set, whether there are any related records in KdTickets.

orenk Old Hand Points: 388 More actions · Answer 2

Thanks for help

That's what know I found...

select y.Anzahl,x.Jahr
from
(select count(*) as Anzahl,
year(Datum) as Jahr
from KdTickets
where Typ = 1
and infonr = 8233
group by year(Datum)
)y
full join
(select year(getdate())-3 as Jahr
union
select year(getdate())-2 as Jahr
union
select year(getdate())-1 as Jahr
union
select year(getdate()) as Jahr) x
on x.Jahr = y.Jahr

sometimes you need just a push...