CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designdatabase-connection
Lutfi Aldi
Lutfi Aldi

Reputation: 463

Get Total Count from Current Status Query MySQL

I have problem to count total data on current status on mysql query.

Briefly, i have 3 tables that relate each other.

m_shift_schedule     site_shift_schedule      m_ticket
================     ===================     ============
* shift_id   ======>  * shift_id             * ticket_id
* start_time          * shift_date           * ticket_status
* end_time            * user_id   ========>  * ticket_served_by

My query:

SELECT user_id,
case when count(m_ticket.ticket_served_by)<2 then 'idle' 
     when count(m_ticket.ticket_served_by)=2 then 'Busy' 
     when count(m_ticket.ticket_served_by)>2 then 'Overload' end as status
FROM site_shift_schedule
LEFT JOIN m_shift_schedule ON site_shift_schedule.shift_id = m_shift_schedule.shift_id
LEFT JOIN m_ticket ON site_shift_schedule.user_id=m_ticket.ticket_served_by
WHERE site_shift_schedule.shift_date =  '2019-02-11' and m_ticket.ticket_status in (4,5,6)
AND CURRENT_TIME > start_time and CURRENT_TIME < end_time
group by user_id

My output from query above:

user_id                           Status
=============================   ============
[email protected]     Idle
[email protected]       Busy

And the question, What should i do if i want show the output become like this:

 Status              Total
==================================
 Idle                 1
 Busy                 1

Upvotes: 2

Views: 150

Answers (2)

Fahmi
Fahmi

Reputation: 37473

You can try below - using subquery

select status,count(user_id) as total
from
(
SELECT user_id,
case when count(m_ticket.ticket_served_by)<2 then 'idle' 
     when count(m_ticket.ticket_served_by)=2 then 'Busy' 
     when count(m_ticket.ticket_served_by)>2 then 'Overload' end as status
FROM site_shift_schedule
LEFT JOIN m_shift_schedule ON site_shift_schedule.shift_id = m_shift_schedule.shift_id
LEFT JOIN m_ticket ON site_shift_schedule.user_id=m_ticket.ticket_served_by
WHERE site_shift_schedule.shift_date =  '2019-02-11' and m_ticket.ticket_status in (4,5,6)
AND CURRENT_TIME > start_time and CURRENT_TIME < end_time
group by user_id
)A group by status

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use case when also in group by

SELECT 
case when count(m_ticket.ticket_served_by)<2 then 'idle' 
     when count(m_ticket.ticket_served_by)=2 then 'Busy' 
     when count(m_ticket.ticket_served_by)>2 then 'Overload' end as status,
     count(*) as total
FROM site_shift_schedule
LEFT JOIN m_shift_schedule ON site_shift_schedule.shift_id = m_shift_schedule.shift_id
LEFT JOIN m_ticket ON site_shift_schedule.user_id=m_ticket.ticket_served_by
WHERE site_shift_schedule.shift_date =  '2019-02-11' and m_ticket.ticket_status in (4,5,6)
AND CURRENT_TIME > start_time and CURRENT_TIME < end_time
group by  case when count(m_ticket.ticket_served_by)<2 then 'idle' 
     when count(m_ticket.ticket_served_by)=2 then 'Busy' 
     when count(m_ticket.ticket_served_by)>2 then 'Overload' end

Upvotes: 0

Related Questions