SQLite Count Summary Query

I'm trying to get a query to summarize each employees work for the week. For example, John Doe did a total of 12 tickets for the week, 4 of which were Break/Fixes, and 4 were Enhancement, and another 4 were uncategorized.

This is what I have so far:

SELECT (users.first_name || ' ' || users.last_name) AS Name,
  COUNT(tickets.id) AS 'Number of Tickets Closed',
  COUNT(tickets.category = 'Maintenance') AS 'Maintenance Tickets',
  COUNT(tickets.category = 'After Hours') AS 'After Hours Tickets',
  COUNT(tickets.category = 'Break Fix') AS 'Break Fix Tickets',
  COUNT(tickets.category = 'Enhancement') AS 'Enhancement Tickets',
  COUNT(tickets.category = '') AS 'Non Categorized Tickets'
FROM tickets, users
ON tickets.assigned_to=users.id
WHERE (tickets.status = 'closed') AND
  (tickets.closed_at >= '2011-07-16 00:00:00') AND
  (tickets.closed_at <= '2011-07-22 23:59:59')
GROUP BY Name;

Here is a sample result:

John Doe1 10 10 10 10 10 10

John Doe2 2 2 2 2 2 2

John Doe3 25 24 24 24 24 24

John Doe4 2 2 2 2 2 2

John Doe5 12 10 10 10 10 10

John Doe6 7 7 7 7 7 7

This query doesn't quite work as I expected it to as all of the columns have the same total (The total number of tickets closed, the following columns seems to only contain the categorized ones.) Help?

EDIT

Just wanted to post the functional code:

SELECT (users.first_name || ' ' || users.last_name) AS Name,
  COUNT(tickets.id) AS 'Number of Tickets Closed',
  COUNT(case tickets.category when 'Maintenance' then 1 else null end) AS 'Maintenance Tickets',
  COUNT(case tickets.category when 'After H开发者_Python百科ours' then 1 else null end) AS 'After Hours Tickets',
  COUNT(case tickets.category when 'Break Fix' then 1 else null end) AS 'Break Fix Tickets',
  COUNT(case tickets.category when 'Enhancement' then 1 else null end) AS 'Enhancement Tickets',
  COUNT(case tickets.category when '' then 1 else null end) AS 'Non Categorized Tickets'
FROM tickets, users
ON tickets.assigned_to=users.id
WHERE (tickets.status = 'closed') AND
  (tickets.closed_at >= '2011-07-16') AND
  (tickets.closed_at <= '2011-07-22')
GROUP BY Name;

---

you may want to use COUNT like this

...
    COUNT(case tickets.category when 'Maintenance' then 1 else null end),
    COUNT(case tickets.category when 'After Hours' then 1 else null end),
...

---

It seems to me you cannot use an alias in the GROUP BY clause. Don't your users have an ID you could use to differenciate them?

And you must use SUM instead of COUNT if you want to count compared with a condition.

SELECT (users.first_name || ' ' || users.last_name) AS Name,
  COUNT(tickets.id) AS 'Number of Tickets Closed',
  SUM(tickets.category = 'Maintenance') AS 'Maintenance Tickets',
  SUM(tickets.category = 'After Hours') AS 'After Hours Tickets',
  SUM(tickets.category = 'Break Fix') AS 'Break Fix Tickets',
  SUM(tickets.category = 'Enhancement') AS 'Enhancement Tickets',
  SUM(tickets.category = '') AS 'Non Categorized Tickets'
FROM tickets, users
ON tickets.assigned_to=users.id
WHERE (tickets.status = 'closed') AND
  (tickets.closed_at >= '2011-07-16 00:00:00') AND
  (tickets.closed_at <= '2011-07-22 23:59:59')
GROUP BY Name;