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

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.

Thanks in advance,

juarrero


0
juarrero
Asked:
juarrero
  • 2
  • 2
  • 2
  • +2
1 Solution
 
spcmnspffCommented:
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
 
Scott PletcherSenior 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
 
Mogalappa AdakiCommented:
your question is not understandable, can you elaborate again clearly your requirement?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
sachin_raoraneCommented:
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

please check your requirment again
0
 
Scott PletcherSenior 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
 
sachin_raoraneCommented:
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
 
juarreroDBAAuthor 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
 
juarreroDBAAuthor 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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now