CODEWITHSUNDEEP
sql
Tony
Tony

Reputation: 12695

SQL Count() and GROUP BY

I have problem with a (simple) SQL query.

The background is, each Book has one (or more) defined BookLanguages

That query returns 9 books which is OK.

SELECT b.BookID
FROM Book b
JOIN BookLanguages bls ON b.BookID = bls.BookID
WHERE b.IsActive= 1 
       AND
        bls.BookLanguageID = 25
GROUP BY b.BookID

But, I must have the total books count which meets that criteria (for pagination purpose).

if I just run that query

SELECT COUNT(b.BookID)
FROM Book b
JOIN BookLanguages bls ON b.BookID = bls.BookID
WHERE b.IsActive= 1 
       AND
        bls.BookLanguageID = 25
GROUP BY b.BookID

I get 9 rows with the 1 value (which is the languages count, because when I remove the AND bls.BookLanguageID = 25 condition, I get 9 rows with values 1 or 2 - because some books have 2 languages defined)

So, the point is - how the get just the 9number instead of the 9 rows ?

Upvotes: 0

Views: 114

Answers (2)

Laurence
Laurence

Reputation: 10976

Remove the group by

Select
  Count(*)
From
  Book b
    Inner Join
  BookLanguages bls 
    On b.BookID = bls.BookID
Where
  b.IsActive= 1 And
  bls.BookLanguageID = 25

Upvotes: 3

xlecoustillier
xlecoustillier

Reputation: 16351

Try with DISTINCT and no GROUP BY :

SELECT COUNT(DISTINCT b.BookID)
FROM Book b
JOIN BookLanguages bls ON b.BookID = bls.BookID
WHERE b.IsActive= 1 
       AND
        bls.BookLanguageID = 25

GROUPing BY Id returns a row for each value of Id, so 9 rows in your case.

Upvotes: 2

Related Questions