Two counts with different status in one group in mysql?

Question

SELECT services, COUNT( * ) AS Total_queue, COUNT( * ) AS balance_queue FROM tqueue WHERE STATUS IN ('8','4') and STATUS ('4') AND get_tiket BETWEEN '2016-11-10 00:00:01' AND '2016-11-10 23:59:59' GROUP BY services

but not working, if just 1 status is working

I want to have 2 result Total_queue with 2 number status (8 and 4) but balance_queue with just one status (4),

DATA TABLE
-----------------------------------
  Total_queue  |   balance_queue   
.-------------------------------------
      ___  7    __   |  ___5_____         
-

Amount 7 in total_queue is produces by two status ( 8 and 4 )
amount 5 in balance_queue is produced by one status ( 4 )

note: status 4 mean queuing-----------------
status 8 mean be called

because of that in total_queue produced by all status ( 8 and 4) queuing and called.

Aren't you missing a "in" or = in your where clause? Can you elaborate the problema a Little more, because I don't finish to undestand what your question is — Nambu14, Nov 09 '16 at 18:49
What exactly is `STATUS ('4')` in your `WHERE` clause? That doesn't seem to be SQL at all. — ebyrob, Nov 09 '16 at 19:08
status 4 just number of status in queue system status i used for called status — Heri, Nov 10 '16 at 00:23

score 3 · Accepted Answer · answered Nov 09 '16 at 19:00

Could you use

SELECT services, 
    SUM( CASE WHEN STATUS IN ('8','4') THEN 1 ELSE 0 END ) AS Total_queue, 
    SUM( CASE WHEN STATUS = '4' THEN 1 ELSE 0 END ) AS balance_queue 
FROM tqueue 
WHERE STATUS IN ('8','4') 
    AND get_tiket BETWEEN '2016-11-10 00:00:01' AND '2016-11-10 23:59:59' 
GROUP BY services

Two counts with different status in one group in mysql?

1 Answers1