CODEWITHSUNDEEP
sqlcount
Sharath
Sharath

Reputation: 2267

Return rows where a field has a specified count

I have a table (DF) 

+----+------+-----+
| ID |  PT  | AVG |
+----+------+-----+
| A  | 1.00 | 2.5 |
| A  | 2.00 | 3.5 |
| B  | 1.00 | 2.8 |
| B  | 2.00 | 2.6 |
| B  | 3.00 | 3.9 |
+----+------+-----+

I am trying to write a simple SQL statement to pull the ID that has 3 points.

I tried these 2 statements but I am doing it wrong 

1. Returns all rows again

SELECT * FROM DF
WHERE PT BETWEEN '1.000' AND '3.000' 

2. Returns counts of rows

SELECT * FROM DF b    
WHERE b.PT=(SELECT COUNT(DISTINCT A.PT) FROM DF A WHERE A.ID=B.ID)

My desired output is:

+----+------+-----+
| ID |  PT  | AVG |
+----+------+-----+
| B  | 1.00 | 2.8 |
| B  | 2.00 | 2.6 |
| B  | 3.00 | 3.9 |
+----+------+-----+

Upvotes: 1

Views: 37

Answers (2)

AdamMc331
AdamMc331

Reputation: 16730

You first need to find out which groups have a count of three rows. You can do that by using a GROUP BY and HAVING clause:

SELECT id
FROM myTable
GROUP BY id
HAVING COUNT(*) = 3;

The HAVING clause is used for conditions regarding aggregation, do not use the WHERE clause for this. Once you have those values, you can go back and select all rows from your table where the id column is IN the result set above:

SELECT *
FROM myTable
WHERE id IN(
   SELECT id
   FROM myTable
   GROUP BY id
   HAVING COUNT(*) = 3);

Here is an SQL Fiddle example using your sample data.

Upvotes: 1

Lamak
Lamak

Reputation: 70668

SELECT *
FROM YourTable
WHERE ID IN (SELECT ID
             FROM YourTable
             GROUP BY ID
             HAVING COUNT(*) = 3);

Upvotes: 1

Related Questions