Database Reference
In-Depth Information
▼
Input
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;
▼
Output
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1003 | 4 |
| 1005 | 2 |
+---------+-----------+
▼
Analysis
This statement warrants an explanation. The first line is a basic
SELECT
using an
aggregate function—much like the examples thus far. The
WHERE
clause filters
all rows with a
prod_price
of at least
10
. Data is then grouped by
vend_id
,
and then a
HAVING
clause filters just those groups with a count of
2
or more.
Without the
WHERE
clause two extra rows would have been retrieved (vendor
1002
that only sells products all priced under
10
, and vendor
1001
that sells
three products but only one of them is priced greater or equal to
10
) as
seen here:
▼
Input
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >= 2;
▼
Output
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+-----------+
