Heri Heri - 28 days ago 19
MySQL Question

Two counts with differents status in one group in mysql?


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.

Answer

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