Issue with grouping - 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 2008 Forums

Transact-SQL (2008)

Issue with grouping

Author

Topic

kwacz23
Starting Member

44 Posts

Posted - 2013-08-08 : 02:14:36

Hi

I have tables where

orderNumber Approval_Process
1 A
1 B
2 A
3 B
4 A
4 B

I would like to get only those orders which have both process (meaning
A and B).

Below query can not help becasue I get also orders which have for example only process A. In second part of my query i try to use AND instead of OR but I do not get data.

with B as (
select ordernumber,approvalprocess,COUNT(ordernumber) number_line from Table
--where approvalprocess='ES_CC' or approvalprocess='ES_ISM'
group by ordernumber,approvalprocess
having COUNT(ordernumber)>1
)

select * from B
where approvalprocess='A' or approvalprocess='B'
order by OrderNumber

COuld you help me ?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-08 : 02:44:38

SELECT orderNumber, COUNT ( DISTINCT Approval_Process) NoOfDistinctProcesses
FROM @tab
GROUP BY orderNumber
HAVING COUNT ( DISTINCT Approval_Process) = 2

--
Chandu

kwacz23
Starting Member

44 Posts

Posted - 2013-08-08 : 03:07:13

Thanks. IT works

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-08 : 03:26:15

quote:
Originally posted by kwacz23

Thanks. IT works

Welcome

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-08 : 06:22:27

This will work so long as you've only A and B as the processes. if you happen to add any more processes say C,D etc then above suggestion wont be sufficient

this is a more generic solution to fetch you only the orders with A and B processes


SELECT orderNumber
FROM @tab
GROUP BY orderNumber
HAVING COUNT ( DISTINCT CASE WHEN Approval_Process IN ('A','B') THEN Approval_Process ELSE NULL END) = 2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources