I have the following SQL expression:
select id, count(oID) from MyTable group by oID;
and I get something like
+-------+---------------+
| id | count(oID) |
+-------+---------------+
| 002 | 6 |
| 104 | 1 |
| 101 | 1 |
| 908 | 1 |
+-------+---------------+
And now I'd like to pull out the rows (select id) where count(oID)
is 1. How can I do this in one SQL statement?
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
The use of COUNT() function in conjunction with GROUP BY is useful for characterizing our data under various groupings. A combination of same values (on a column) will be treated as an individual group.
COUNT() with HAVINGThe HAVING clause with SQL COUNT() function can be used to set a condition with the select statement.
To count the number of rows, use the id column which stores unique values (in our example we use COUNT(id) ). Next, use the GROUP BY clause to group records according to columns (the GROUP BY category above). After using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause.
Use a HAVING
clause to filter an aggregated column.
SELECT id, count(oID)
FROM MyTable
GROUP BY oID
HAVING count(oID) = 1
UPDATE 1
wrap the results in a subquery
SELECT a.*
FROM tableName a INNER JOIN
(
SELECT id
FROM MyTable
GROUP BY id
HAVING count(oID) = 1
) b ON a.ID = b.ID
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With