0

tblticket

id issue status
1 test 1
2 test 2
3 test 3

i want to find count based on status 1,2,3 and so on

i write 3 sql statment as follows

select count(*) from ticket where status=1
select count(*) from ticket where status=2
select count(*) from ticket where status=3

any way to simplify this into single query

Suriyan Suresh
  • 2,964
  • 14
  • 51
  • 80

2 Answers2

4

The simple answer is

select count(*), status
from ticket
group by status

However, this gives different results if you have no rows for a status because the behaviour documented here: Does COUNT(*) always return a result?

Assuming only 3 status values you'd do this:

select count(t.status), s.status
from
   (SELECT 1 AS status UNION  ALL SELECT 2 UNION  ALL SELECT 3) s
   LEFT JOIN
   ticket t ON s.status = t.status 
group by
   s.status

..or with a separate status lookup table:

select  count(t.status), s.status
from
   (SELECT DISTINCT status FROM status) s
   LEFT JOIN
   ticket t ON s.status = t.status 
group by
   s.status
Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
3
SELECT status, COUNT(*)
FROM tblticket
GROUP BY status

If you really only want those three statuses, you could also do:

SELECT status, COUNT(*)
FROM tblticket
WHERE status IN (1, 2, 3)
GROUP BY status
Yuck
  • 49,664
  • 13
  • 105
  • 135