CASE Question - SQL Server Forums

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)

CASE Question

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-10 : 10:15:22

Hi

I have a table which contains number of stock groups along with their stock category as shown below:

Stock Group Stock_Category
BE 1505
BE 1506
BE 1515
BE 773
BE 792
BE 811
BE 847
BE 848
BE 911
CAB 7600
DIU 1034
DIU 1086
DIU 12
DIU 25
DIU 28
DIU 1086A
INS 3601
INS 4011
LPC 9700
MO TC130
SEE SERCO
TSB 1033B
UK 2620
UK 2663
UK 2673
UK 2966
UK 2971

Each of the stock groups belongs to a higher stock group family:
BER contains BE/INS/UK
OG contains CAB/LPC/MO/SEE
OD contains DFI/DIU

I am trying to do a count in the table to match each record to the relevant higher stock group family. I have used the follwoing CASE statement to do so:

SELECT
CASE WHEN [Stock Group] IN ('BE','INS','UK') THEN 'BER'
WHEN [Stock Group] IN ('CAB','LPC','MO','SEE') THEN 'OG'
WHEN [Stock Group] IN ('DFI,DIU') THEN 'OD'
END AS 'MainStockGroup'
,COUNT(*)
FROM StockGroups
GROUP BY [Stock Group]

I get the following results: (Please note the number figures are not accurate)

MainStockGroup
BER 81
OD 1
OG 10
BER 2
OD 1
OG 1
OD 1

The problem I am having here is that the higher stock group family is being used is being used twice with different counts.

Does anyone know how to combine the counts so the output is similar to?

BER 83
OG 11
OD 3

Thanking you in advance!!!

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-10 : 10:18:35

Hi

Sorry my imported data appeared wrong. The stock category is the 2 or 3 letter code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-10 : 12:27:57

Change like this and try:-

SELECT t.MainStockGroup,COUNT(*)
FROM
(
SELECT 
CASE WHEN [Stock Group] IN ('BE','INS','UK') THEN 'BER'
WHEN [Stock Group] IN ('CAB','LPC','MO','SEE') THEN 'OG'
WHEN [Stock Group] IN ('DFI,DIU') THEN 'OD'
END AS 'MainStockGroup'
,*
FROM StockGroups)t
GROUP BY t.MainStockGroup

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources