Solved

select, group by, having clauses

Posted on 2002-06-04
8
302 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

808 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