Solved

select, group by, having clauses

Posted on 2002-06-04
8
300 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
  • 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
 
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 300 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now