[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

select, group by, having clauses

Posted on 2002-06-04
8
Medium Priority
?
307 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 70

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 70

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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…
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.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

650 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