How to perform query operation on another query result

Posted on

Question :

How to get approved , unapproved , deleted count from the below result using sql query.

SELECT approved,deleted_at,count(id)AS count FROM `payment_voucher` 
    WHERE company_id = 1 GROUP BY approved,deleted_at;

I’m attaching the above-queried result as an image.

enter image description here

Desired output: Approved:32 not_approved:4 deleted:7

Answer :

SELECT SUM(CASE WHEN (approved=1) AND (deleted_at IS NULL) THEN `count` END) approved,
       SUM(CASE WHEN (approved=0) AND (deleted_at IS NULL) THEN `count` END) not_approved,
       SUM(CASE WHEN deleted_at IS NOT NULL THEN `count` END) deleted
FROM (your subquery) source

Leave a Reply

Your email address will not be published. Required fields are marked *