February 3, 2011 at 1:55 pm
Hello, I am trying to write a query against the following data:
Table: #OrdCnt
order_id state_id
FnF5000400000000000000001 2
FnF5000400000000000000001 3
FnF5000400000000000000002 3
FnF5000400000000000000005 3
FnF5000400000000000000006 2
FnF5000400000000000000007 2
FnF5000400000000000000007 3
FnF5000400000000000000008 2
I need a query that will return a count of distinct order id's using the following criteria:
1) ALL state_id values MUST be either a 2 OR a 3 but NOT only 3. The state_id CAN be only 2's
For example using the above data I would expect a count of 4.
The data in bold would be excluded.
Any clever ideas on how to accomplish this? Thank you!
Best Regards,
~David
February 3, 2011 at 1:58 pm
Is this question highly simplified?
The reason I ask is a WHERE clause for where the status = 2 will return you your order ID's that have a 2 ( or 2 and 3, but that's duplicative).
From there you just re-join with that distinct list of order_ids and pull your records back.
So, something like:
SELECT
*
FROM
tbl JOIN ( SELECt DISTINCT order_id FROM tbl WHERE status = 2) AS drv
ON tbl.Order_id = drv.Order_id
If you'd like better tested code, however, please check out the first link in my signature. It'll make your data/ddl consumable to us for easier testing.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 5, 2011 at 2:38 pm
Hi.
Solution provided so far will not work.
You can use a neat trick with CASE nested in the SUM() aggregate function.
With that trick, solution is really simple, and even without joins:
Prepare the data (you should have been posted that!):
create table #OrdCnt
( order_id varchar(50),
state_id int
)
insert into #OrdCnt( order_id, state_id )
select order_id = 'FnF5000400000000000000001', state_id = 2
union all select 'FnF5000400000000000000001', 3
union all select 'FnF5000400000000000000002', 3
union all select 'FnF5000400000000000000005', 3
union all select 'FnF5000400000000000000006', 2
union all select 'FnF5000400000000000000007', 2
union all select 'FnF5000400000000000000007', 3
union all select 'FnF5000400000000000000008', 2
And the solution is:
select order_id
from #OrdCnt
group by order_id -- guarantees that order_id is unique
having sum(case when state_id = 3 then 1 else 0 end) < count(*)
Output:
order_id
FnF5000400000000000000001
FnF5000400000000000000006
FnF5000400000000000000007
FnF5000400000000000000008
It is easier to understand the code if we add few columns, just for explanation purpose:
select order_id,
count_star = count(*),
count_state_3 = sum(case when state_id = 3 then 1 else 0 end)
from #OrdCnt
group by order_id
Output:
order_id count_star count_state_3
FnF5000400000000000000001 2 1
FnF5000400000000000000002 1 1
FnF5000400000000000000005 1 1
FnF5000400000000000000006 1 0
FnF5000400000000000000007 2 1
FnF5000400000000000000008 1 0
count_state_3 counts rows in the group (with the same order_id) that have state_id = 3.
With HAVING clause we just filter out rows that we need.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply