Help with GROUP BY where NULLs will exist

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with GROUP BY where NULLs will exist

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-12-21 : 13:12:10

Please see the following code to create a table of costs with regional currency data:


DECLARE @tmp TABLE (costEventID int, regionInfoID int, costAmount money, date datetime)
DECLARE @regionInfo TABLE (regionInfoID int identity(1,1), regionDesc nvarchar(20))

INSERT INTO @regionInfo (regionDesc) VALUES ('en-GB')
INSERT INTO @regionInfo (regionDesc) VALUES ('en-US')
INSERT INTO @regionInfo (regionDesc) VALUES ('no-NO')

INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-11-15')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 2, 1000, '2009-11-15')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 3, 1000, '2009-11-15')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-11-08')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 2, 1000, '2009-10-23')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-09-03')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-03-19')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-03-18')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2008-11-15')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2008-12-16')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 3, 1000, '2009-10-10')

SELECT
	t.date,
	t.costAmount,
	r.regionDesc
FROM 
	@tmp t INNER JOIN
	@regionInfo r ON t.regionInfoID = r.regionInfoID

What I need to be able to do is to show the cost totals on a monthly basis for EACH region that exists in @regionInfo. What I also need is to show zero where a cost does not exist for a region. For example:


Month/Year      Total Cost    Region
-------------------------------------
October 2009    0             en-GB
October 2009    0             en-US
October 2009    1000          no-NO
November 2009   2000          en-GB
November 2009   1000          en-US
November 2009   1000          no-NO
....

Can anyone please help me to write the code for this? I'm completely stuck...!

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-21 : 13:23:03

Editing for wrongness!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-21 : 13:42:31

you will need something like this



DECLARE @tmp TABLE (costEventID int, regionInfoID int, costAmount money, date datetime)
DECLARE @regionInfo TABLE (regionInfoID int identity(1,1), regionDesc nvarchar(20))

INSERT INTO @regionInfo (regionDesc) VALUES ('en-GB')
INSERT INTO @regionInfo (regionDesc) VALUES ('en-US')
INSERT INTO @regionInfo (regionDesc) VALUES ('no-NO')
INSERT INTO @regionInfo (regionDesc) VALUES ('ka-MARS')

INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-11-15')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 2, 1000, '2009-11-15')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 3, 1000, '2009-11-15')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-11-08')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 2, 1000, '2009-10-23')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-09-03')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-03-19')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-03-18')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2008-11-15')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2008-12-16')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 3, 1000, '2009-10-10')

SELECT DATENAME(month, t.date) + ' ' + DATENAME(year, t.date) As MonthYear,
	   SUM(ISNULL(t.costAmount,0)),
	   r.regionDesc
 FROM @regionInfo r 
 LEFT JOIN @tmp t 
   ON r.regionInfoID = t.regionInfoID
GROUP BY DATENAME(month, t.date) + ' ' + DATENAME(year, t.date), regionDesc

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-21 : 14:04:15

I think this is more what he was looking for, having 0 sum for each region. I tried the above and it nulls the wrong value...

DECLARE @tmp TABLE (costEventID int, regionInfoID int, costAmount money, date datetime)
DECLARE @regionInfo TABLE (regionInfoID int identity(1,1), regionDesc nvarchar(20))

INSERT INTO @regionInfo (regionDesc) VALUES ('en-GB')
INSERT INTO @regionInfo (regionDesc) VALUES ('en-US')
INSERT INTO @regionInfo (regionDesc) VALUES ('no-NO')

INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-11-15')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 2, 1000, '2009-11-15')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 3, 1000, '2009-11-15')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-11-08')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 2, 1000, '2009-10-23')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-09-03')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-03-19')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2009-03-18')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2008-11-15')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 1, 1000, '2008-12-16')
INSERT INTO @tmp (costEventID, regionInfoID, costAmount, date) VALUES (1, 3, 1000, '2009-10-10')

SELECT
r.date as Date,
isnull(sum(t.costAmount),0) as SumCost,
r.region
FROM
(Select Distinct
ri.RegionInfoID as RID,
regionDesc as Region,
datename(month,t2.date) as date
FROM @regioninfo ri cross join
@tmp t2 ) as r
LEFT OUTER JOIN
@tmp t ON t.regionInfoID = r.RID and datename(month,t.date) = r.date

Group by
r.date,
r.region
order by 1,3

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-21 : 15:13:14

even better if that is what R wants

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-12-22 : 03:43:46

Hi

Thanks for your posts. Yes the CROSS JOIN approach is what I was looking for. Just one more thing though...

Can this code be expanded to also include months where no costs exist? I imagine we would have to use a further temporary table to hold all possible months from the earliest to the most recent dates that exist in @tmp, and CROSS JOIN that to @regionInfo??

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-22 : 09:04:31

Yes, basically you want to create a table that contains all combinations that you want to see in your results and then LEFT JOIN to that. You will have to generate the Months table seperately.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-12-22 : 09:43:35

Okay I made a new table variable and inserted the necessary months (basically taking the MIN and MAX date values from @tmp, and filling in between).

The problem I have is that my final statement shows NULLs in the t.regionDesc column. I wondered why this is, and whether I need to join the @regionInfo table again using INNER JOIN to get these values??

Here's what I have so far, the solution may be much more obvious to you...


SELECT 
	isnull(sum(t.costAmount),0),
	t.regionDesc,
	DATENAME(MONTH, d.theDate) + ' ' + CONVERT(nvarchar, YEAR(d.theDate))
FROM
	@regionCultures r CROSS JOIN 
	@dates d LEFT OUTER JOIN
	@tmp t ON r.regionInfoID = t.regionInfoID AND d.thedate = DATEADD(MONTH, DATEDIFF(MONTH, 0, t.[date]), 0)
GROUP BY
	d.theDate,
	r.regionInfoID,
	t.regionDesc

R
Constraint Violating Yak Guru

328 Posts

Posted - 2009-12-22 : 12:26:17

Hi again

I finally (think) I've got it working. To get around the Null issue above I simply added a column to the @regionCultures table to include the regionDesc data.

What I would appreciate from you maestros is any feedback on how the query can be better written. My attempt is below, but I'm sure there are more effecient ways that the better-experienced could share with me...


SELECT 
	SUM(COALESCE(t.costAmount,0)),
	r.regionCultureID,
	r.regionCultureCode,
	DATENAME(MONTH, d.theDate) + ' ' + CONVERT(nvarchar, YEAR(d.theDate))
FROM
	@regionCultures r CROSS JOIN 
	@dates d LEFT OUTER JOIN
	@tmp t ON r.regionCultureID = t.regionCultureID AND datename(month,t.date) = datename(month,d.thedate) AND datename(year,t.date) = datename(year,d.thedate)
GROUP BY
	d.thedate,
	r.regionCultureID,
	r.regionCultureCode

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources