CODEWITHSUNDEEP
mysqlsql
Md. Himel Ali
Md. Himel Ali

Reputation: 311

Count total, active and inactive data by MySQL

My code sample for get total total_stamp, i need with active and inactive. My stamp table have current_status row for active and inactive

SELECT r.*
     , COUNT(s.current_status) total_stamp
  FROM tbl_registers r
  LEFT 
  JOIN tbl_stamps s
    ON r.register_id = s.register_id
 WHERE r.ins_id = 1
 GROUP 
    BY r.register_id
 ORDER BY r.register_name_en ASC
     , s.stamp_name_en ASC

enter image description here

Current output like that, I need another more column line one is total_active another is total inactive with single query.

Upvotes: 1

Views: 2959

Answers (2)

0x11
0x11

Reputation: 183

I think the best way to achieve this would be to split your LEFT JOIN up into two separate LEFT JOINS. One to the table where active and another where inactive. This way you will be able to sum the three separately. Does that make sense? Something like this:

SELECT r.*, sActive.total + sInactive.total as total_stamp, sActive.total as active_stamp, sInactive.total as inactive_stamp
FROM tbl_registers as r
LEFT JOIN (
    SELECT register_id, COUNT(*) as total
    FROM tbl_stamps
    WHERE s.current_status = 'active'
    GROUP BY register_id
) as sActive ON sActive.register_id = r.register_id
LEFT JOIN (
    SELECT register_id, COUNT(*) as total
    FROM tbl_stamps
    WHERE s.current_status = 'inactive'
    GROUP BY register_id
) as sInactive ON sInactive.register_id = r.register_id
GROUP BY r.register_id

Upvotes: 1

O. Jones
O. Jones

Reputation: 108796

SELECT r.*, 
       COUNT(s.current_status), 
       SUM(current_status='something meaning active') active,
       SUM(current_status='something meaning inactive') inactive,
       ...

should do the trick. Why? because expressions like current_status='something meaning inactive' in MySQL have the value 0 meaning false, or 1 meaning true. So SUM() adds up the number of true items.

Upvotes: 3

Related Questions