SQL query to count rows based on condition? Keep getting syntax error for my count logic

select sum(bookID) as totalbooks, count(bookstatus = R) as returnedbooks
from library with (nolock)
where 
   librarylocation = 'Chesterfield'
   --and bookstatus = R

So I want my query to return how many books total as a column, but how many books are returned as a column. I’m using count also applying a condition, but I’m getting error saying:

Incorrect syntax near ‘)’.

Anyway to achieve this?

Answer

Here is the syntax error count(bookstatus = R)

You can convert it like below

select sum(bookID) as totalbooks, SUM(CASE WHEN bookstatus = R THEN 1 ELSE 0 END) as returnedbooks
from library with (nolock)
where librarylocation = 'Chesterfield'
--and bookstatus = R