CODEWITHSUNDEEP
sqlcountgroup-byaggregate-functionswhere-clause
Ruzzy
Ruzzy

Reputation: 67

SQL using a WHERE statement with an aggregate function

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.

Upvotes: 2

Views: 185

Answers (3)

user1192724
user1192724

Reputation: 529

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

Upvotes: 0

ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells

Reputation: 66662

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

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425623

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;

Upvotes: 2

Related Questions