MySQL count with group by

Question

Sorry for an unclear question, my english isnt that good. So theres my query:

SELECT ticketID, 
status,
COUNT(status) as count,
statusName,
assign   
FROM ticket, department, status 
WHERE ticket.department = 100 
AND ticket.department = department.departID 
AND ticket.status = status.statusID
GROUP BY statusName,assign

and this is the result:

| ticketID   | count       | statusName | assign |
|:----------|-----------:|:---------:|:-------:
| 1002       |           2 |       open |   NULL |
| 1020       |           1 |       open |  James |
| 1021       |           1 |       open |   Nick |
| 1015       |           1 |    overdue |   NULL |

My goal was to count ticket by their status, and if status='open' and assign = null then the status would change to 'unassigned', i need a better solution or just a way to merge the result where 'James' and 'Nick' to be one, as i only need to know whether the ticket is assigned or not.

Answer 1

Don't see where you need department table, think you can remove it safely, but...

SELECT a.statusID, a.Status, Count(*) 
FROM(
   SELECT statusID,
   (CASE WHEN statusName = 'open' and assign IS NULL THEN 'unassigned' 
         WHEN statusName ='open' and assign IS NOT NULL THEN 'assigned'
         ELSE statusName
         END) as Status
   FROM ticket 
   INNER JOIN department ON ticket.department = department.departID 
   INNER JOIN status ON ticket.status = status.statusID
   WHERE ticket.department = 100) as a
GROUP BY a.Status, a.statusID

SqlFiddle (simplified)

Answer 2

SELECT ticketID, 
status,
COUNT(status) as count,
statusName,
'unassigned'   
FROM ticket, department, status 
WHERE ticket.department = 100 
AND ticket.department = department.departID 
AND ticket.status = status.statusID
AND assign is NULL
GROUP BY statusName
UNION
SELECT ticketID, 
status,
COUNT(status) as count,
statusName,
'assigned'   
FROM ticket, department, status 
WHERE ticket.department = 100 
AND ticket.department = department.departID 
AND ticket.status = status.statusID
AND assign is NOT NULL
GROUP BY statusName

This query fetches the unassigned and assigned tickets in the form of a union of two disjoint resultsets.

Answer 3

SELECT ticketID,status,sum(CASE status 
    WHEN 'open' then 1
    WHEN '' then 1
    WHEN NULL then 1
    else 0) as count,statusName,assign   
        FROM ticket, department, status WHERE ticket.department = 100 
        AND ticket.department = department.departID 
        AND ticket.status = status.statusID
        GROUP BY statusName,assign