Group By using a Date
-
The following query is used to return a count of records grouped by the month of a datetime column. This works properly except this is used for graphing purposes, so I need any months that may not have values in the table to be returned as 0.
Is there a way to construct the SELECT statement to return a 0 for non-represented months? Currently the only method I have found to accomplish this is to insert the result set into a table object that has been prepopulated with a 0 record for each month.
This is the current query:
SELECT DatePart(mm,DatePkgSent) AS [Month],Count(DatePkgSent) AS AmountSent
FROM PotentialClients P
WHERE DatePart(yy,DatePkgSent)=@Year
GROUP BY DatePart(m,DatePkgSent)
-
You will need something to provide the dates but you can join to the table rather than insert.
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control. -
You could have several possibilities
1:)
SELECT MM.MonthNum AS [Month],
Count(DatePkgSent) AS AmountSent
FROM PotentialClients P right join
(select 1 as MonthNum
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12) as MM on DatePart(mm,DatePkgSent) = MonthNum
WHERE DatePart(yy,DatePkgSent)=@Year
GROUP
BY MonthNum
2:)
Here is a variant
select 1 AS [Month],
Count(DatePkgSent) AS AmountSent
FROM PotentialClients P
WHERE DatePart(yy,DatePkgSent) = @Year
AND DatePart(mm,DatePkgSent) = 1
UNION ALL
select 2,
Count(DatePkgSent)
FROM PotentialClients P
WHERE DatePart(yy,DatePkgSent) = @Year
AND DatePart(mm,DatePkgSent) = 2
UNION ALL
select 3,
Count(DatePkgSent)
FROM PotentialClients P
WHERE DatePart(yy,DatePkgSent) = @Year
AND DatePart(mm,DatePkgSent) = 3
UNION ALL
select 4,
Count(DatePkgSent)
FROM PotentialClients P
WHERE DatePart(yy,DatePkgSent) = @Year
AND DatePart(mm,DatePkgSent) = 4
UNION ALL
select 5,
Count(DatePkgSent)
FROM PotentialClients P
WHERE DatePart(yy,DatePkgSent) = @Year
AND DatePart(mm,DatePkgSent) = 5
UNION ALL
select 6,
Count(DatePkgSent)
FROM PotentialClients P
WHERE DatePart(yy,DatePkgSent) = @Year
AND DatePart(mm,DatePkgSent) = 6
UNION ALL
select 7,
Count(DatePkgSent)
FROM PotentialClients P
WHERE DatePart(yy,DatePkgSent) = @Year
AND DatePart(mm,DatePkgSent) = 7
UNION ALL
select 8,
Count(DatePkgSent)
FROM PotentialClients P
WHERE DatePart(yy,DatePkgSent) = @Year
AND DatePart(mm,DatePkgSent) = 8
UNION ALL
select 9,
Count(DatePkgSent)
FROM PotentialClients P
WHERE DatePart(yy,DatePkgSent) = @Year
AND DatePart(mm,DatePkgSent) = 9
UNION ALL
select 10,
Count(DatePkgSent)
FROM PotentialClients P
WHERE DatePart(yy,DatePkgSent) = @Year
AND DatePart(mm,DatePkgSent) = 10
UNION ALL
select 11,
Count(DatePkgSent)
FROM PotentialClients P
WHERE DatePart(yy,DatePkgSent) = @Year
AND DatePart(mm,DatePkgSent) = 11
UNION ALL
select 12,
Count(DatePkgSent)
FROM PotentialClients P
WHERE DatePart(yy,DatePkgSent) = @Year
AND DatePart(mm,DatePkgSent) = 12
3:)
This one is a kind of pivot table
select Jan = sum(case when DatePart(mm,DatePkgSent) = 1 then 1 else 0 end),
Feb = sum(case when DatePart(mm,DatePkgSent) = 2 then 1 else 0 end),
Mar = sum(case when DatePart(mm,DatePkgSent) = 3 then 1 else 0 end),
Apr = sum(case when DatePart(mm,DatePkgSent) = 4 then 1 else 0 end),
May = sum(case when DatePart(mm,DatePkgSent) = 5 then 1 else 0 end),
Jun = sum(case when DatePart(mm,DatePkgSent) = 6 then 1 else 0 end),
Jul = sum(case when DatePart(mm,DatePkgSent) = 7 then 1 else 0 end),
Aug = sum(case when DatePart(mm,DatePkgSent) = 8 then 1 else 0 end),
Sep = sum(case when DatePart(mm,DatePkgSent) = 9 then 1 else 0 end),
Oct = sum(case when DatePart(mm,DatePkgSent) = 10 then 1 else 0 end),
Nov = sum(case when DatePart(mm,DatePkgSent) = 11 then 1 else 0 end),
Dec = sum(case when DatePart(mm,DatePkgSent) = 12 then 1 else 0 end)
WHERE DatePart(yy,DatePkgSent)=@Year
GROUP
BY DatePart(mm,DatePkgSent)
I hope that helps
Bye
Gabor
Bye
Gabor -
Sorry Tim,
in the third solution (the pivot) you don't have to put the group by clause.
It has been a typo (copy/past) error.
Bye
Gabor
Bye
Gabor -
Thanks for the suggestions.
I had not considered one of the variants Gabor suggested. Always helpful to see new ways to accomplish the task. Since performance is not an issue in this particular query, I am going 'keep it simple' and go with something similar to your third option - which is similar to what I'm currently using.
-
Create a temporary table prepopulated with twelve rows, one for each month, and then do an outer join on this table to provide the missing month numbers.
-
You can also stick the select statment into a view.
create view MonthList
as
Select 1 and MonthNum
union
...
I have done this many times to reflect small amounts of static data without a permanent or temporary table needed. Always use what makes the most sense in your environment.
Steve Hughes
Magenic Technologies
-
1. Create a table, say Months (MonthNo varchar(2)); populate this table with numbers 1 to 12, representing the months
2. Query now becomes:
SELECT M.MonthNo AS [Month], Count(P.DatePkgSent) AS AmountSent
FROM PotentialClients P, Months M
WHERE DatePart(yy, P.DatePkgSent)=@Year
and DatePart (mm, P.DatePkgSent)=* M.MonthNo
GROUP BY M.MonthNo
(Note: I used the old-fashioned =* to indicate an OUTER join. Also note that the query may issue a "Warning: Null value eliminated from aggregate.").
gdefi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply