Refactor subqueries using GROUP BY/HAVING?

Question

I'm building a MySQL query to determine how many items from each of several categories appear in a given date range. My initial attempt looked like this:

select Title, 
  (select count(*) from entries where CategoryID=1
    and Date >= @StartDate and Date <= @EndDate) as Cat1,
  (select count(*) from entries where CategoryID=2
   and Date >= @StartDate and Date <= @EndDate) as Cat2,
  (select count(*) from entries where CategoryID is null
   and Date >= @StartDate and Date <= @EndDate) as UnkownCategory
from entries
  where Date >= @StartDate and Date <= @EndDate

The table is quite large and I'd like to refactor the query to speed it up, but I'm not sure how - can this be rewritten using GROUP BY/HAVING statements or is there another way I'm missing?

Edit: Sample result set - something like this:

Title | Category 1 Total | Category 2 Total | Unknown Category Total
ABC     1                  3                  0
DEF     2                  7                  2

Answer 1

Select COUNT(*), sTitle, CategoryID FROM entries 
WHERE Date >= @StartDate and Date <= @EndDate 
GROUP BY CategoryID, sTitle

Answer 2

How about grouping by category id then using the having statement to filter out specific categories, like:

select CategoryID, count(*) 
from entries 
where Date >= @StartDate AND Date <= @EndDate
group by CategoryID
having CategoryID = 1 or CategoryID = 2 or CategoryID is null

If there are multiple titles per category you could group by both fields:

select Title, CategoryID, count(*) 
from entries 
where Date >= @StartDate AND Date <= @EndDate
group by Title, CategoryID
having CategoryID = 1 or CategoryID = 2 or CategoryID is null

Answer 3

select Title, SUM(CategoryID=1) as Cat1, SUM(categoryID=2) as Cat2,
SUM(categoryID IS NULL) as UnknownCategory
FROM entries
WHERE Date BETWEEN @StartDate AND @EndDate
GROUP BY Title

You can stick expressions in sum() functions: truth equals 1, false equals 0. Also I used the BETWEEN operator which is a little faster.

An alternative that would return a different result layout but is a little conceptually simpler:

select Title, CategoryID, count(*)
from entries
WHERE Date BETWEEN @StartDate AND @EndDate
group by Title, CategoryID