phpmyadmin Question posted in
The official documentation can be found here.

Make multiple counts then sum it in one query – Phpmyadmin

I’m seeking for solution on how to get count for 3 faculty code then sum all the count in one variable. I didn’t found the right query to combine the count and then sum it.

This is the code that I have:

SELECT COUNT(CASE WHEN `fac_code` LIKE '%JABPN%' THEN 1 END) AS count1,
       COUNT(CASE WHEN `fac_code` LIKE '%JABFNT%' THEN 1 END) AS count2,
       COUNT(CASE WHEN `fac_code` LIKE '%FPKF%' THEN 1 END) AS count3
FROM `list_faculty` WHERE active = 'Y';

And the output that I got:

count1 count2 count3
69 184 36

The output that I need is the sum of all the count which is 289

Tags:

2

Answers


  1. 0

    How about adding this?

    COUNT(CASE WHEN fac_code LIKE '%FPKF%' OR fac_code LIKE %JABFNT%' OR fac_code LIKE  '%JABPN%' THEN 1 END)

    SQL doesn’t allow you to use aliases in the same SELECT, so the alternative would be a CTE or subquery.

    Login or Signup to reply.
  2. 0

    You can combine these three counts in one condition of case .. when or you can use + between them as follows:

    SELECT COUNT(CASE WHEN `fac_code` LIKE '%JABPN%'
                    OR `fac_code` LIKE '%JABFNT%'
                    OR `fac_code` LIKE '%FPKF%' 
                  THEN 1 END) AS count1_3
FROM `list_faculty` WHERE active = 'Y';

    OR you can use + between them as follows:

    SELECT COUNT(CASE WHEN `fac_code` LIKE '%JABPN%' THEN 1 END) +
       COUNT(CASE WHEN `fac_code` LIKE '%JABFNT%' THEN 1 END) +
       COUNT(CASE WHEN `fac_code` LIKE '%FPKF%' THEN 1 END) AS count1_3
FROM `list_faculty` WHERE active = 'Y';

    And the best way in your case is to use condition in WHERE clause as follows:

    SELECT COUNT(*) 
  FROM `list_faculty` 
 WHERE active = 'Y'
   AND (`fac_code` LIKE '%JABPN%'
        OR `fac_code` LIKE '%JABFNT%'
        OR `fac_code` LIKE '%FPKF%')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search