July 7, 2011 at 5:51 pm
Hi helpers,
Could you please help me with the following?
How can I filter the following sample?
No GroupID Code Type
1 3 10 Red
2 3 11 Red
3 3 11 Red
4 3 15 Blue
5 4 10 Red
6 4 11 Red
7 4 15 Blue
8 4 11 Blue
...
I only want to see the records where count of Code >=2 group by GroupID
however, if and only if count of same type >= 2 group by GroupID
So my result should be No: 1,2,3,5,6,7,8
Any ideas?
Thank you in advance!
July 7, 2011 at 9:31 pm
Were these the type of results that you were needing?
SELECT *
FROM Table_Name
WHERE Code IN (
SELECT Code
FROM Table_Name
GROUP BY Code
HAVING COUNT(*) >= 2
)
Jason
Webmaster at SQL Optimizations School
July 7, 2011 at 9:42 pm
I don't think so, Jason. Your query returns all rows. You may be misreading his requirements.
John, can you please explain to us why number 7 in your sample shouldn't be omitted? Although there are two rows with type 'Blue' in group 4, row 7 is the only code 15 row within that group. This doesn't appear to meet your requirement below
where count of Code >=2 group by GroupID
I have a similar issue with row 1. It is the only code 10 row within group 3.
Did you mean to say where there are are at least two rows of the same type within each [groupID], and also at least two rows of the same code when grouping by [type]?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 7, 2011 at 10:18 pm
How about this?
; with TableCTE AS
(
SELECT *
FROM
( VALUES
(1, 3, 10, 'Red')
,(2, 3, 11, 'Red')
,(3, 3, 11, 'Red')
,(4, 3, 15, 'Blue')
,(5, 4, 10, 'Red')
,(6, 4, 11, 'Red')
,(7, 4, 15, 'Blue')
,(8, 4, 11, 'Blue')
) A ( c1 , c2, c3, c4)
),
groupdata as
(
select * , grpcnt = COUNT(*) over ( PARTITION BY c2 , c4)
from TableCTE
)
select *
from groupdata
where grpcnt >= 2
order by c1
July 8, 2011 at 3:15 am
Can you explain better?
July 8, 2011 at 3:49 am
--I made a mistake...Sorry
I only want to see the records where count of Code >=2 group by GroupID
however, if and only if count of same type >= 2 group by GroupID
Sorry Correction... So my result should be No: 5,6,7,8 because there are 2 or more Red and Blue. Eventhough Group 3 has Red it only has 1 blue, so it does not meet the if and only if criteria. The code could be any...
July 8, 2011 at 4:53 am
[font="Times New Roman"]
Hi,
Check the following script:
SELECT Number
FROM TABLE_NAME
WHERE code
IN
(
SELECT code
FROM TABLE_NAME
GROUP BY Type
HAVING COUNT(*)>=2
)
GROUP BY code
HAVING COUNT(*)>2
Regards,
Anil K
[/font]
July 8, 2011 at 5:46 am
[font="Times New Roman"]
Hi,
I am not clear with your requirement.
If count of code should be greater then or equals to two while grouping by groupid then 5th and 7th record could not be included in the resultset as their code is unique within the group.
The reason that i understand is, code for 7th record should be 10 not 15.
Regards,
Anil K
[/font]
July 8, 2011 at 6:31 am
JohnDBA (7/8/2011)
--I made a mistake...SorryI only want to see the records where count of Code >=2 group by GroupID
however, if and only if count of same type >= 2 group by GroupID
Sorry Correction... So my result should be No: 5,6,7,8 because there are 2 or more Red and Blue. Eventhough Group 3 has Red it only has 1 blue, so it does not meet the if and only if criteria. The code could be any...
I'm sorry, but I still don't see how 5, 6, 7, 8 is a valid result set given your stated requirements.
Row 5 is the one and only code 10 in group 4 and row 7 is the one and only code 15 in group 4. It seems to me that your stated requirements would produce only rows 6 and 8.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 8, 2011 at 6:38 am
[font="Times New Roman"]
First check your table data.
Regards,
Anil k
[/font]
July 8, 2011 at 6:59 am
Thank you all for your help!
Yes, it is very confusing. But I got some clarifications.
Condition for the type should be two or more of the same type per groupID.
So,
GroupId = 3
3 = Red but it only have 1 = Blue so, I do not want to see GroupID 3 on my results because it only have 1 Blue
GroupID = 4
2 = Red and 2 = Blue so then I want to see groupID 4
In another example:
GroupID = 5 (Could be)
3 = Red and 2 = Blue so I want to see groupID 5 as well
So, I want to see groupId of those that meet that criteria of >= 2 of 2 of eatch type per GroupID.
Does it make sence now?
Thank you so much! John
July 8, 2011 at 8:00 am
We're getting there. You only want to see groupIDs that have at least 2 members of each type. But what about this case?
Group 99 Red= 2, Blue = 2, Green = 1
Would you want to see group 99 or not? It has two types with 2 or more rows, but only 1 green.
Once we get types sorted out, we'll get back to codes.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 8, 2011 at 8:05 am
Good Point, yes I want to see GroupID 99 as well, because it has 2 or more sets of two ro more.
July 8, 2011 at 10:49 am
How about this then?
; with TableCTE AS
(
SELECT *
FROM
( VALUES
(1, 3, 10, 'Red')
,(2, 3, 11, 'Red')
,(3, 3, 11, 'Red')
,(4, 3, 15, 'Blue')
,(5, 4, 10, 'Red')
,(6, 4, 11, 'Red')
,(7, 4, 15, 'Blue')
,(8, 4, 11, 'Blue')
) A ( c1 , c2, c3, c4)
),
groupdata as
(
select oute.* , grpcnt = COUNT(*) over ( PARTITION BY c2 , c4)
, distinctgrpcont = ( select COUNT(distinct inn.c4)
from TableCTE inn
where inn.c2 = oute.c2
)
from TableCTE oute
)
select *
from groupdata outertable
where grpcnt >= 2
AND NOT EXISTS ( select 1
from groupdata existscheq
where existscheq.grpcnt < 2
and outertable.c2 = existscheq.c2
)
order by c1
I looks very ugly , i know, but given my time crunch i can get this far. If possible i shall try to twaek this guy a bit..
July 8, 2011 at 11:54 am
Okay... so we want to see any group with two or more types that have two or more rows per type.
Now tell us again how codes fit into that. I assume that we are still doing our counts within a single group ID. But do we just eliminate codes that have only a single row within the group, or do we eliminate groups that don't have at least two sets of codes with a count of two or more?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply