SELECT CASE - too many results

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)

SELECT CASE - too many results

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-31 : 10:44:53

The following query works perfectly EXCEPT it gives me 12 results, 1 for each line. But if you notice, there are only about 4 o 5 actual values. How can I get it to roll up the values, like all the 'Office XP' into one so I can have the 4 to 5 results?

SELECT
(
select case Product
When 'Microsoft Office 2000 SR-1 Professional' then 'Office 2000'
When 'Microsoft Office Enterprise 2007' then 'Office 2007'
When 'Microsoft Office Professional 2007' then 'Office 2007'
When 'Microsoft Office Professional Edition 2003' then 'Office 2003'
When 'Microsoft Office Professional Plus 2007' then 'Office 2007'
When 'Microsoft Office Small Business 2007' then 'Office 2007'
When 'Microsoft Office Small Business Edition 2003' then 'Office 2003'
When 'Microsoft Office Standard Edition 2003' then 'Office 2003'
When 'Microsoft Office XP Professional' then 'Office XP'
When 'Microsoft Office XP Professional with FrontPage' then 'Office XP'
When 'Microsoft Office XP Small Business' then 'Office XP'
When 'Microsoft Office XP Standard' then 'Office XP'
Else Product
end ) Product,
Count(Product) InstallsCount
From OfficeInstallations
Where Product NOT IN (
	'Microsoft Office 2003 SP2',
	'Microsoft Office Professional 2007 Trial',
	'Microsoft Office Shared 64-bit MUI (English) 2007',
	'Microsoft Office Shared MUI (English) 2007',
	'Microsoft Office Shared Setup Metadata MUI (English) 2007',
	'Microsoft Office Small Business Connectivity Components'
)
GROUP BY Product
HAVING Count(WorkstationID) > 0
ORDER BY Product

These are the actual results

Product	InstallsCount
Office 2000	138
Office 2003	2
Office 2003	95037
Office 2003	5
Office 2007	72
Office 2007	19692
Office 2007	3
Office 2007	95
Office XP	41
Office XP	3
Office XP	1
Office XP	2781

Thank you for any help.

Duane

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 10:54:56

May be this?


select Product, Count(Product) as InstallCount
From
(
select case 
When Product in ('Microsoft Office 2000 SR-1 Professional') then 'Office 2000'
When Product in ('Microsoft Office Enterprise 2007', 'Microsoft Office Professional 2007','Microsoft Office Professional Plus 2007', 'Microsoft Office Small Business 2007') then 'Office 2007'
When Product in ('Microsoft Office Professional Edition 2003', 'Microsoft Office Small Business Edition 2003', 'Microsoft Office Standard Edition 2003') then 'Office 2003'
When Product in ('Microsoft Office XP Professional', 'Microsoft Office XP Professional with FrontPage', 'Microsoft Office XP Small Business', 'Microsoft Office XP Standard') then 'Office XP'
Else Product end as Product, *
From OfficeInstallations
Where Product NOT IN (
	'Microsoft Office 2003 SP2',
	'Microsoft Office Professional 2007 Trial',
	'Microsoft Office Shared 64-bit MUI (English) 2007',
	'Microsoft Office Shared MUI (English) 2007',
	'Microsoft Office Shared Setup Metadata MUI (English) 2007',
	'Microsoft Office Small Business Connectivity Components')
) t
Group by Product
Having Count(WorkstationID) > 0
ORDER BY Product

Harsh Athalye
http://www.letsgeek.net/

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-31 : 11:21:05

Thank you for the reply, but though I was encouraged, it did not work at all.
First of all, I got this:
The column 'Product' was specified multiple times for 't'
Then, I simplified it and tried just the case syntax 'When product in' and it through an error there as well.

Duane

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 11:27:06

I can understand the multiple duplicate column error and it can be easily removed. But what error did you get with Case syntax?

Harsh Athalye
http://www.letsgeek.net/

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-03-31 : 12:40:03

Actually, your Case statement DID work, and the only problem was that the statement:

Else Product end as Product, *

had to be changed to:

Else Product end as Product, WorkstationID

I don't understand why, and I am not sure how you would have fixed the multiple duplicate column error, but would like to so I can understand better. Thank you for your effort.

Duane

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 12:45:14

quote:
Originally posted by duanecwilson

Actually, your Case statement DID work, and the only problem was that the statement:

Else Product end as Product, *

had to be changed to:

Else Product end as Product, WorkstationID

I don't understand why, and I am not sure how you would have fixed the multiple duplicate column error, but would like to so I can understand better. Thank you for your effort.

Duane

I was referring to the same thing. Since column name "Product" was already there, entering * resulted in the error. So replacing * with WorkstationID is what I would have done too.

Harsh Athalye
http://www.letsgeek.net/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources