December 14, 2015 at 12:44 am
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
December 14, 2015 at 1:09 am
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.
December 14, 2015 at 2:01 am
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...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply