Unique values in a case statement

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

Transact-SQL (2000)

Unique values in a case statement

Author

Topic

darthasshat
Starting Member

17 Posts

Posted - 2008-01-28 : 19:20:51

I have a case statement that is supposed to have 5 element rows and then one "other" row that is supposed to catch all others for each day. So 6 rows for each day. I'm getting multiple "other" rows for each day. Can I not aggregate these into one row? Here is my code, any help would be greatly appreciated. Thanks!


SELECT DISTINCT 
                      VOC.datetime2 AS UsageDate, 
                      CASE VOC.nvarchar4 WHEN 'ink' THEN 'ink' WHEN 'oil' THEN 'oil' WHEN 'blanket wash' THEN 'blanket wash' WHEN 'fountain solution' THEN 'fountain solution'
                       WHEN 'solvent' THEN 'solvent' ELSE 'other' END AS MaterialType, SUM(CAST(VOC.nvarchar12 AS dec)) AS [Usage], 
                      SUM(CAST(VOC.nvarchar12 AS dec) * MSDS.float1) AS VOCEmissions, VOC.nvarchar18 AS FormName
FROM         dbo.UserData VOC INNER JOIN
                      dbo.UserData MSDS ON VOC.nvarchar2 = MSDS.nvarchar2
WHERE     (VOC.tp_ListId = '{2798766F-595A-4301-B788-D724487F37CD}') AND (MSDS.tp_ListId = '{08FDE859-DA4B-4716-B334-68BC208760BC}') AND 
                      (VOC.bit1 = 1)
GROUP BY VOC.nvarchar4, VOC.datetime2, VOC.nvarchar18

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-28 : 20:10:46

you need to GROUP BY the CASE statement instead of the column in the CASE statmenet. And you don't need the DISTINCT since you are using GROUP BY:

sorry, I re-arranged the format so I could read it.


SELECT	VOC.datetime2 AS UsageDate, 
	CASE VOC.nvarchar4 
		WHEN 'ink' THEN 'ink' 
		WHEN 'oil' THEN 'oil' 
		WHEN 'blanket wash' THEN 'blanket wash' 
		WHEN 'fountain solution' THEN 'fountain solution'
		WHEN 'solvent' THEN 'solvent' 
		ELSE 'other' 
		END AS MaterialType, 
	SUM(CAST(VOC.nvarchar12 AS dec)) AS [Usage], 
	SUM(CAST(VOC.nvarchar12 AS dec) * MSDS.float1) AS VOCEmissions, 
	VOC.nvarchar18 AS FormName
FROM	dbo.UserData VOC INNER JOIN
		dbo.UserData MSDS ON VOC.nvarchar2 = MSDS.nvarchar2
WHERE   VOC.tp_ListId = '{2798766F-595A-4301-B788-D724487F37CD}'
AND	MSDS.tp_ListId = '{08FDE859-DA4B-4716-B334-68BC208760BC}'
AND	VOC.bit1 = 1
GROUP BY VOC.datetime2, 
	VOC.nvarchar18,
	CASE VOC.nvarchar4 
		WHEN 'ink' THEN 'ink' 
		WHEN 'oil' THEN 'oil' 
		WHEN 'blanket wash' THEN 'blanket wash' 
		WHEN 'fountain solution' THEN 'fountain solution'
		WHEN 'solvent' THEN 'solvent' 
		ELSE 'other' 
		END

Be One with the Optimizer
TG

darthasshat
Starting Member

17 Posts

Posted - 2008-01-29 : 10:49:04

You are amazing! Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources