How to count combine data value

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

General SQL Server Forums

New to SQL Server Programming

How to count combine data value

Author

Topic

pliskin
Starting Member

3 Posts

Posted - 2011-03-28 : 03:37:40

Hi everybody,

I need some help here.
For example, I have these kind of data :

FieldA FieldB
VendA W10
VendB W20
VendC W10
VendD W30
VendE W40
VendF W10
VendG W50
VendH W40
VendI W50

I want to count the fieldB, but with this condition:
W30, W40 and W50 will be count as W30
So, the output should be like this :
W10 3
W20 1
W30 5

Can anybody help me how to create a sql syntax to produce output like that?

Thanks in advance.

Devart
Posting Yak Master

102 Posts

Posted - 2011-03-28 : 03:57:44

Hello,

For example:

WITH your_table AS(
SELECT 'VendA' AS FieldA, 'W10' AS FieldB
UNION ALL
SELECT 'VendB' AS FieldA, 'W20' AS FieldB
UNION ALL
SELECT 'VendC' AS FieldA, 'W10' AS FieldB
UNION ALL
SELECT 'VendD' AS FieldA, 'W30' AS FieldB
UNION ALL
SELECT 'VendE' AS FieldA, 'W40' AS FieldB
UNION ALL
SELECT 'VendF' AS FieldA, 'W10' AS FieldB
UNION ALL
SELECT 'VendG' AS FieldA, 'W50' AS FieldB
UNION ALL
SELECT 'VendH' AS FieldA, 'W40' AS FieldB
UNION ALL
SELECT 'VendI' AS FieldA, 'W50' AS FieldB
)

SELECT FieldB, Count(*)
FROM (SELECT CASE WHEN Cast(Replace(FieldB,'W','') AS INT)>=30 THEN 'W30' ELSE FieldB END AS fieldB FROM your_table) t
GROUP BY FieldB;

Devart,
SQL Server Tools:
dbForge Data Studio
dbForge Schema Compare
dbForge Data Compare
dbForge SQL Complete

pliskin
Starting Member

3 Posts

Posted - 2011-03-28 : 05:29:32

Thanks a lot Devart, your code is work. Now, I can customize it to fulfill my requirment.

Once again, thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources