Solved

# select, group by, having clauses

Posted on 2002-06-04
299 Views
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.

juarrero

0
Question by:juarrero
• 2
• 2
• 2
• +2

LVL 5

Expert Comment

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

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

your question is not understandable, can you elaborate again clearly your requirement?
0

LVL 5

Expert Comment

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

0

LVL 69

Expert Comment

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

sachin_raorane earned 300 total points
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

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

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

### Suggested Solutions

table fragmentation 40 73
sql server query? 6 26
SQL 2016 Setup - Connectivity Issues 4 11
Azure SQL DB? 3 12
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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

#### Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!