?
Solved

select, group by, having clauses

Posted on 2002-06-04
8
Medium Priority
?
306 Views
Last Modified: 2012-08-14
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
Comment
Question by:juarrero
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7054359
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7054964
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
 

Expert Comment

by:Mogalappa Adaki
ID: 7055395
your question is not understandable, can you elaborate again clearly your requirement?
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 5

Expert Comment

by:sachin_raorane
ID: 7056349
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7056427
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
 
LVL 5

Accepted Solution

by:
sachin_raorane earned 900 total points
ID: 7061174
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
 

Author Comment

by:juarrero
ID: 7156763
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
 

Author Comment

by:juarrero
ID: 7156766
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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question