Delegate group - 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

Development Tools

ASP.NET

Delegate group

Author

Topic

thalz142
Starting Member

7 Posts

Posted - 2008-02-05 : 21:53:04

Hi all i ve problem to combine data..
This is my table data..

part totjam
____ ___
B4B3 2
B4A11 4.1
B5A11 4
B5B3 4.1
B6C1 6
B6A10 6.5

is't posibble to retrieve my query like this?
_________________________________________
Group X<3 3<X<5 5<X<7
_________________________________________
B4 1 1 0
_________________________________________
B5 0 2 0
_________________________________________
B6 0 0 2
_________________________________________

can i grouped my group where first row sum all 'B4%' and 2nd row sum * 'B5%' and 3rd row sum * 'B6%'

I've try this query but it will display separate group..

SELECT     part, SUM(CASE WHEN totjam <= '4' THEN 1 ELSE 0 END) AS [X<= '4'], SUM(CASE WHEN totjam >= '5' THEN 1 ELSE 0 END) AS [X >= '5']
FROM         TABLE
GROUP BY part

I ve problem to grouped part field as delegate as B4,B5,B6...
I dont ve idea to display my data like this

_________________________________________
Group X<3 3<X<5 5<X<7
_________________________________________
B4 1 1 0
_________________________________________
B5 0 2 0
_________________________________________
B6 0 0 2
_________________________________________

any idea???many thx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-10 : 22:26:22

[code]DECLARE @sample TABLE
(
part varchar(10),
totjam decimal(10,2)
)

INSERT INTO @sample
SELECT 'B4B3', 2 UNION ALL
SELECT 'B4A11', 4.1 UNION ALL
SELECT 'B5A11', 4 UNION ALL
SELECT 'B5B3', 4.1 UNION ALL
SELECT 'B6C1', 6 UNION ALL
SELECT 'B6A10', 6.5

SELECT [Group] = left(part, 2),
[X < 3] = COUNT(CASE WHEN totjam < 3 THEN 1 END),
[3 < X < 5] = COUNT(CASE WHEN totjam >= 3 AND totjam < 5 THEN 1 END),
[5 < X < 7] = COUNT(CASE WHEN totjam >= 5 AND totjam < 7 THEN 1 END)
FROM @sample
GROUP BY left(part, 2)

/*
Group X < 3 3 < X < 5 5 < X < 7
----- ----------- ----------- -----------
B4 1 1 0
B5 0 2 0
B6 0 0 2

(3 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources