Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Scott
Posting Yak Master
145 Posts |
 Posted - 2002-01-14 : 13:31:13
|
| I have a GROUP BY query on month(datetime).Is there an easy way to ensure that all months are shown consecutively for the selected range.eg: If my query returns results from January to June but March and May have no data they will not apear in the result set. Is there a way to get them there with (zero or null) values.If no quick way, anyone with any ideas on how to approach this.Thanks |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-14 : 13:39:26
|
| Try:SELECT month, sum(isnull(data,0))FROM tableGROUP BY monthwhere 'data' is the data you are trying to select (I just used sum as an example, I don't know what you are using).HTH-Chad |
 |
|
|
Scott
Posting Yak Master
145 Posts |
Posted - 2002-01-14 : 13:43:29
|
| Sorry to make myself clearer:Dates for March and May don't exist, either null's or zero's are ok in the result set. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-14 : 13:53:20
|
| You'd have to create a separate table to hold the months:CREATE TABLE Months(MonthName varchar(12) NOT NULL, MonthNum smallint NOT NULL)INSERT INTO Months (MonthName, MonthNum) VALUES ('January', 1)INSERT INTO Months (MonthName, MonthNum) VALUES ('February', 2)INSERT INTO Months (MonthName, MonthNum) VALUES ('March', 3)INSERT INTO Months (MonthName, MonthNum) VALUES ('April', 4)INSERT INTO Months (MonthName, MonthNum) VALUES ('May', 5)INSERT INTO Months (MonthName, MonthNum) VALUES ('June', 6)INSERT INTO Months (MonthName, MonthNum) VALUES ('July', 7)INSERT INTO Months (MonthName, MonthNum) VALUES ('August', 8)INSERT INTO Months (MonthName, MonthNum) VALUES ('September', 9)INSERT INTO Months (MonthName, MonthNum) VALUES ('October', 10)INSERT INTO Months (MonthName, MonthNum) VALUES ('November', 11)INSERT INTO Months (MonthName, MonthNum) VALUES ('December', 12)Then tweak Chad's answer a little:SELECT M.monthname, sum(isnull(T.data,0))FROM Months M LEFT JOIN table T ON (M.MonthNum=Month(T.dateCol))GROUP BY M.monthname |
|
|
|
|
|
|
|
|
Group By month