NULL records not grouping but staying separate.

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 2008 Forums

Transact-SQL (2008)

NULL records not grouping but staying separate.

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-07-09 : 11:46:23

I have the following query that works nicely except if the LOCATION_CITY is blank or NULL. Then, I get a bunch of detail lines with different (QTY) and (COST). I want these to aggregate as if they were all ONE city. Here's the query:

USE SoftwareReporting
GO
SELECT ISNULL(NULLIF(COMPONENT_DESC,''),'*************** GRAND TOTAL ***************') AS COMPONENT_DESC
, CASE  WHEN LOCATION_STATE IS NOT NULL 
		THEN ISNULL(NULLIF(LOCATION_CITY,''),'*** STATE TOTAL ***') 
		ELSE '' END AS CITY
, CASE  WHEN COMPONENT_DESC IS NOT NULL
		THEN ISNULL(NULLIF(LOCATION_STATE,''),'********** COMPONENT_DESC TOTAL **********')
		ELSE '' END AS STATE
, SUM(ISNULL([QTY],0)) QTY
, SUM(ISNULL([QTY],0)*ISNULL([COST],0)) EXT_COST
FROM SR_HISTORY_20100707_CITY_STATE
GROUP BY ROLLUP (
COMPONENT_DESC
, LOCATION_STATE
, LOCATION_CITY 
)
ORDER BY CASE WHEN COMPONENT_DESC IS NULL THEN 1 ELSE 0 END
, COMPONENT_DESC
 ,CASE WHEN LOCATION_STATE IS NULL THEN 1 ELSE 0 END
 ,LOCATION_STATE
 ,CASE WHEN LOCATION_CITY IS NULL THEN 1 ELSE 0 END
 ,LOCATION_CITY
GO

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources