Using CASE with MS Query in Excel

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

Other Forums

Other Topics

Using CASE with MS Query in Excel

Author

Topic

poolmwv
Starting Member

12 Posts

Posted - 2007-12-20 : 11:58:32

I finally figured out the Syntax to the CASE Statement in Excel, but I don't quite get the results I am looking for.

Here is the essence of the statement:
SELECT (CASE
WHEN ENTRY='Fan' THEN 'PC'
WHEN ENTRY='Mouse' THEN 'PC'
WHEN ENTRY='Floppy Drive' THEN 'PC'
ELSE ENTRY END) , COUNT(*)
FROM CATEGORIES,INCIDENTS WHERE INCIDENTS.ID_CATEGORY = CATEGORIES.ID
GROUP BY ENTRY
ORDER BY COUNT(*) DESC

However, I get the results:
Printer 11
PC 2
Monitor 2
PC 1
Paper Trays 1

I would like to have only one "PC" with a value of 3. (In this instance 2 were originally "Fan" and 1 was "Mouse".

I thought I'd just put in AS EQUIPMENT after the closed parentheses at the end of the CASE statement and before the comma, then GROUP BY EQUIPMENT.

I can put in "AS EQUIPMENT", but EQUIPMENT does not show up as a header for that column. But once I put in GROUP BY EQUIPMENT I get an error of "Incorrect Column expression" and it shows the CASE statement.

Any suggestions?

I'm not an idiot, but I play one on the net.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 12:02:41

You are grouping by original entry anyway but displays as "PC".

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 12:05:00

[code]SELECT [Entry],
COUNT(*) AS Items
FROM (
SELECT CASE
WHEN {c or i}.ENTRY IN ('Fan', 'Mouse', 'Floppy Drive') THEN 'PC'
ELSE {c or i}.ENTRY
END AS [Entry]
FROM CATEGORIES AS c
INNER JOIN INCIDENTS AS i ON i.ID_CATEGORY = c.ID
) AS d
GROUP BY [Entry]
ORDER BY COUNT(*) DESC,
[Entry][/code]

E 12°55'05.25"
N 56°04'39.16"

poolmwv
Starting Member

12 Posts

Posted - 2007-12-20 : 12:09:59

Wow! Such a fast reply! Thanks Peso! I think this will do the trick.

I'm not an idiot, but I play one on the net.

poolmwv
Starting Member

12 Posts

Posted - 2007-12-20 : 12:33:45

oops! One last question. I should have included my where statement but I was trying to be brief. I have the following Where statement:

WHERE ((CATEGORIES.ID>=972 And CATEGORIES.ID<=990) AND (INCIDENTS.DT_CREATED>={ts '2007-10-01 00:00:00'} And INCIDENTS.DT_CREATED<{ts '2007-10-31 23:59:59'}))

I tried putting it before the close parentheses before AS d

and I tried putting it after AS d. I took the parentheses out. I only did the dates. No matter what I tried, I received an error: Could not add the table '('

I know it's something simple, but I'm just not seeing it.

I'm not an idiot, but I play one on the net.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 13:05:29

{c or i} means you have to remove the brackets and put either c or i in front of the point.
It depends on which table the ENTRY column is stored.

SELECT		[Entry],
		COUNT(*) AS Items
FROM		(
			SELECT		CASE 
						WHEN {c or i}.ENTRY IN ('Fan', 'Mouse', 'Floppy Drive') THEN 'PC'
						ELSE {c or i}.ENTRY
					END AS [Entry]
			FROM		CATEGORIES AS c
			INNER JOIN	INCIDENTS AS i ON i.ID_CATEGORY = c.ID
			WHERE		c.ID >= 972
					AND c.ID <=990
					AND i.DT_CREATED >= '2007-10-01'
					AND i.DT_CREATED < '2007-11-01'
		) AS d
GROUP BY	[Entry]
ORDER BY	COUNT(*) DESC,
		[Entry]

E 12°55'05.25"
N 56°04'39.16"

poolmwv
Starting Member

12 Posts

Posted - 2007-12-20 : 14:03:35

Woohoo! Perfect! Thank you, thank you, thank you!

I'm not an idiot, but I play one on the net.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources