• Status: Solved
• Priority: Medium
• Security: Public
• Views: 315

# select, group by, having clauses

Hello,

I would need help with the GROUP BY and HAVING clauses.

Suppose I have the following table: mytable

field1   field2
------   ------
a        1
a        2
a        2
b        1
b        2
b        3
c        1
c        2

First I want to select those rows such that field1 is repeated 3 times:

SELECT *
FROM mytable
WHERE field1 in (SELECT field1
FROM mytable
GROUP BY field1
HAVING count(*) = 3)

The result must be:

field1   field2
------   ------
a        1
a        2
a        2
b        1
b        1
b        1

Then I would like to select from this set those rows such that field2 is "2" twice:

The result should be:

field1   field2
------   ------
a        1
a        2
a        2

But... I don not know how to build a statement to achieve this, which includes the GROUP BY clause.

juarrero

0
juarrero
• 2
• 2
• 2
• +2
1 Solution

Commented:
Try this . . .

Select *
from MyTable
Where Field2 In

( Select T1.Field2
From (SELECT *
FROM mytable
WHERE field1 in (SELECT field1
FROM mytable
GROUP BY field1
HAVING count(*) = 3)) T1
Group By T1.field2 Having Count(T1.*) = 2)
0

Senior DBACommented:
I don't think comparing only on field2 will work :=(.  Maybe try this:

SELECT myTable.*
FROM myTable
INNER JOIN
(SELECT field1
FROM myTable
GROUP BY field1
HAVING COUNT(*) = 3)
AS t1 ON myTable.field1 = t1.field1
INNER JOIN
(SELECT field2
FROM myTable t2
WHERE t1.field1 = t2.field1
GROUP BY field2
HAVING COUNT(*) = 2)
AS t3 ON myTable.field1 = t3.field1 AND myTable.field2 = t3.field2

Running short on time.  Will try to further verify this query later.
0

Commented:
your question is not understandable, can you elaborate again clearly your requirement?
0

Commented:
in your first result u have given
field1 field2
------ ------
a      1
a      2
a      2
b      1
b      1
b      1

in case of "a" is ok, but how you will get

b      1
b      1
b      1

when there is no such data in table

0

Senior DBACommented:
I suspected that it was just a "typo" and that he meant to repeat the original data, like so:

b   1
b   2
b   3

Since no value appears twice, "b" should not be selected.

0

Commented:
As i said there is no data like you want in first result, so i doubt you can form such query.

for the second result following Query will work

select * from mytable
where field1 in(
select field1
from mytable
where field2 in
(select field2 from mytable
group by field2
having count(*)>=2)
group by field1
having count(*) = 3)
0

DBAAuthor Commented:
Sorry for the long delay...

Effectively there is a typo, the first result in my query should be:

field1 field2
------ ------
a      1
a      2
a      2
b      1
b      2
b      3

Then, the desired query should be more or less like the proposed one:

select * from mytable
where field2 in(
select field2
from mytable
where field1 in
(select field1 from mytable
group by field1
having count(*)=3)
group by field2
having count(*) = 2)

Thanks and sorry again

juarrero
0

DBAAuthor Commented:
Sorry for the large delay...

Effectively there is a typo, the first result should be:

field1 field2
------ ------
a      1
a      2
a      2
b      1
b      2
b      3

Then, the desired query should be more or less like the proposed one:

select * from mytable
where field2 in(
select field2
from mytable
where field1 in
(select field1 from mytable
group by field1
having count(*)=3)
group by field2
having count(*) = 2)

Thanks and sorry again

juarrero
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.