Grouping by TWO parameters - 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

General SQL Server Forums

New to SQL Server Programming

Grouping by TWO parameters

Author

Topic

niko79542
Starting Member

7 Posts

Posted - 2012-12-17 : 15:28:48

DECLARE @blabla TABLE
(
id int not null,
color char(6) null,
price real null
)
INSERT @blabla
VALUES
(101, 'Blue', 12),
(102, 'Red', 10),
(103, 'Red', 10),
(104, 'Red', 10),
( 202, 'Red', 30),
( 202, 'Red', 30),
( 202, 'Red', 35),
( 205, 'Blue', 40)

select
left(id, 2)
from
@blabla
group by
left(id, 2)
Having
Count(Left(id, 2)) > 1
and stdev(price) <> 0

Hello,

This above query searches groups ID's where the left two numbers match, the count is > 1, and the price has a standard deviation other than 0 (in other words the prices do not match.

Problem: I would like to group by both left(id, 2) AND color. (Ex. all 10x and Reds) So for the 10x identifiers, The red one's all have the same price, so I do not want 10 to show up in the query. For the 20x identifiers, the red ones have different prices (stdev <>0) [30 30 35].

Expected result-->

(no column name)
20

shilpash
Posting Yak Master

103 Posts

Posted - 2012-12-17 : 15:46:11

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-17 : 21:02:42

[code]DECLARE @blabla TABLE
(
id int not null,
color char(6) null,
price decimal(10,4) null
)
INSERT @blabla VALUES (101, 'Blue', 12)
INSERT @blabla VALUES (102, 'Red', 10)
INSERT @blabla VALUES (103, 'Red', 10)
INSERT @blabla VALUES (104, 'Red', 10)
INSERT @blabla VALUES (202, 'Red', 30)
INSERT @blabla VALUES (202, 'Red', 30)
INSERT @blabla VALUES (202, 'Red', 35)
INSERT @blabla VALUES (205, 'Blue', 40)

;with CTE as
(
Select *
,COUNT(1) OVER (PARTITION BY Left(id,2),Color) as CNT
,Stdev(price) Over (Partition By Left(id,2),Color)Std
from @blabla
)

Select * from CTE
Where std <> 0 and CNT > 1[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources