SQL using a WHERE statement with an aggregate function

Question

The following code runs exactly the way I want it to. Now I need to achieve the same task but only showing results with a COUNT greater than 1. The COUNT is meant to count the amount of rows in the allocation table of each distinct book id (bid).

Prompt Task 1E7;
SELECT B.bid,title,COUNT(*)
FROM ALLOCATION A
INNER JOIN BOOK B
ON A.bid = B.bid
GROUP BY B.bid,title
ORDER BY COUNT(*),bid;

I tried simply putting a WHERE COUNT(*) > 1 in a few different locations. This just returns the error "SQL command not properly ended" for the WHERE line. I also tried the following.

Prompt Task 1E7;
SELECT B.bid,title,COUNT(*)
FROM ALLOCATION A
INNER JOIN BOOK B
ON A.bid = B.bid
GROUP BY B.bid,title
WHERE (SELECT COUNT(*)
FROM ALLOCATION A
INNER JOIN BOOK B
ON A.bid = B.bid
GROUP BY B.bid,title) > 1
ORDER BY COUNT(*),bid;

and any other number of little tweaks.

When you use group by, you usually are going to use `having`, instead of `where`. Look up the difference. — Isaac Fife, Apr 24 '12 at 16:37

score 2 · Answer 1 · answered Apr 24 '12 at 16:34

2

Aggregates can be filtered with HAVING:

SELECT  B.bid, title, COUNT(*) cnt
FROM    ALLOCATION A
INNER JOIN
        BOOK B
ON      A.bid = B.bid
GROUP BY
        B.bid, title
HAVING  COUNT(*) > 1
ORDER BY
        cnt, bid;

answered Apr 24 '12 at 16:34

Quassnoi

413,100
91
616
614

score 0 · Accepted Answer · answered Apr 24 '12 at 16:35

0

You want the HAVING clause, which filters by values after the rollup in GROUP BY

answered Apr 24 '12 at 16:35

ConcernedOfTunbridgeWells

64,444
15
143
197

Shit. I kid you not, I just discovered HAVING. T__T – Ruzzy Apr 24 '12 at 16:36

score 0 · Answer 3 · answered Apr 24 '12 at 16:40

0

You need to use the HAVING clause just like other posters suggested. For future reference, this is a very good reference for SQL commands: http://www.1keydata.com/sql/sql.html

answered Apr 24 '12 at 16:40

user1192724

529
2
7
15

SQL using a WHERE statement with an aggregate function

3 Answers3