group by - 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)

group by

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-02-13 : 15:01:09

i have this table


OrderNo  status
1         AA
1         BB
2         AA
2         AA
3         AA
4         BB

i want to know how many order in each status(GROUP BY)
the problem is if the order have staus BB AND AA than all order is BB
(like OrderNo 1 he have 2 rows AA,BB )
i want to get


2 BB
2 AA

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-13 : 17:31:08

[code]DECLARE @t TABLE (OrderNo INT, status CHAR(2))
INSERT @t VALUES(1, 'BB')
INSERT @t VALUES(1, 'AA')
INSERT @t VALUES(2, 'AA')
INSERT @t VALUES(2, 'AA')
INSERT @t VALUES(3, 'AA')
INSERT @t VALUES(4, 'BB')
INSERT @t VALUES(5, 'AA')
INSERT @t VALUES(5, 'BB')
INSERT @t VALUES(6, 'BB')
INSERT @t VALUES(6, 'BB')

SELECT COUNT(DISTINCT OrderNo), status FROM @t t1 WHERE CASE WHEN status = 'AA' THEN CASE WHEN NOT EXISTS (SELECT * FROM @t t2 WHERE t2.OrderNo = t1.OrderNo AND t2.status = 'BB') THEN 1 ELSE 0 END ELSE 1 END = 1 GROUP BY status

/*
----------- ------
2 AA
4 BB
*/
[/code]

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-02-14 : 01:00:17

and how i get this table:
if orderNo have status aa and bb than bb became aa


OrderNo  status
1         AA
1         AA
2         AA
2         AA
3         AA
4         BB

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-14 : 01:19:21

Exchange 'AA' with 'BB' in my posted query.

Sachin.Nand

2937 Posts

Posted - 2010-02-14 : 02:45:34

Here is my shot at it


DECLARE @t TABLE (OrderNo INT, status CHAR(2))
INSERT @t VALUES(1, 'AA')
INSERT @t VALUES(1, 'AA')
INSERT @t VALUES(2, 'AA')
INSERT @t VALUES(2, 'AA')
INSERT @t VALUES(3, 'AA')
INSERT @t VALUES(4, 'BB')
INSERT @t VALUES(5, 'AA')
INSERT @t VALUES(5, 'BB')
INSERT @t VALUES(6, 'BB')
INSERT @t VALUES(6, 'BB')


select count(cnt),status from
(
			select COUNT(DISTINCT t3.status)cnt,t3.status from @t t1 
								cross apply (select top 1 (orderno)as ord,status  from @t t2 where t1.OrderNo=t2.OrderNo Order by status desc )t3
								group by t3.ord,t3.status
)t group by status

PBUH

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-14 : 03:13:07

That's a nice idea!

quote:

Originally posted by Idera

Here is my shot at it


DECLARE @t TABLE (OrderNo INT, status CHAR(2))
INSERT @t VALUES(1, 'AA')
INSERT @t VALUES(1, 'AA')
INSERT @t VALUES(2, 'AA')
INSERT @t VALUES(2, 'AA')
INSERT @t VALUES(3, 'AA')
INSERT @t VALUES(4, 'BB')
INSERT @t VALUES(5, 'AA')
INSERT @t VALUES(5, 'BB')
INSERT @t VALUES(6, 'BB')
INSERT @t VALUES(6, 'BB')


select count(cnt),status from
(
			select COUNT(DISTINCT t3.status)cnt,t3.status from @t t1 
								cross apply (select top 1 (orderno)as ord,status  from @t t2 where t1.OrderNo=t2.OrderNo Order by status desc )t3
								group by t3.ord,t3.status
)t group by status

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-14 : 03:33:46

[code]
;With CTE (OrderNo,Status,Ord)
AS
(
(SELECT OrderNo,Status,CASE WHEN status THEN 1 ELSE 0 END
FROM YourTable)

SELECT COUNT(DISTINCT c.OrderNo),c.Status
FROM CTE c
INNER JOIN (SELECT OrderNo,MAX(Ord) AS MaxOrd
FROM CTE
GROUP BY OrderNo) c1
ON c.OrderNo=c1.OrderNo
AND c.Ord=c1.MaxOrd
GROUP BY c.Status
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources