cPanel Question posted in
The official documentation can be seen here.

Multiple COUNT(*) display on one page – CPanel

This is probably super simple – but i am tearing my hair out.
database is using cPanel (MariaDB 10.1.28)

Heres the scenario

  • 1 Table Called “WARNINGS”

  • 1 column called “WARNING_REASON” (VARCHAR)
    warning_reason contains various “reasons”

  • 1 column called “WARNING_LEVEL” (VARCHAR)
    This contains either “VERBAL”, “LEVEL 1” or “LEVEL 2”

I need to output this data in php on a 5 column table

  • Warning Reason
  • Total number of rows in table grouped by warning_reason
  • Total of rows that match “VERBAL” grouped by warning_reason
  • Total of rows that match “LEVEL 1” grouped by warning_reason
  • Total of rows that match “LEVEL 2” grouped by warning_reason
Tags:

2

Answers


  1. 0

    You can use subqueries to accomplish this:

    SELECT t1.warning_reason
, (SELECT COUNT(*) FROM warnings s1 WHERE s1.warning_reason = t1.warning_reason) AS total_count
, (SELECT COUNT(*) FROM warnings s2 WHERE s2.warning_reason = t1.warning_reason AND s2.warning_level = 'VERBAL') AS verbal_count
, (SELECT COUNT(*) FROM warnings s3 WHERE s3.warning_reason = t1.warning_reason AND s3.warning_level = 'LEVEL 1') AS level1_count
, (SELECT COUNT(*) FROM warnings s4 WHERE s4.warning_reason = t1.warning_reason AND s4.warning_level = 'LEVEL 2') AS level2_count
FROM warnings t1
GROUP BY t1.warning_reason
    Login or Signup to reply.
  2. 0

    You can use conditional aggregation:

    select warning_reason,
    count(*) as total_warnings,
    sum(warning_level = 'VERBAL')  as verbal_warnings,
    sum(warning_level = 'LEVEL 1') as level1_warnings,
    sum(warning_level = 'LEVEL 2') as level2_warnings
from warnings
group by warning_reason

    This is shorter and probably faster than running multiple subselects.

    Note that in MySQL boolean expression like warning_level = 'VERBAL' return either 1 or 0 (or NULL if warning_level is NULL).

    If you want a standard compliant query you can change the line to

    sum(case when warning_level = 'VERBAL' then 1 else 0 end) as verbal_warnings,

    or 

    sum(case warning_level when 'VERBAL' then 1 else 0 end) as verbal_warnings,

    or 

    count(case warning_level when 'VERBAL' then 1 else null end) as verbal_warnings,

    since NULL is the default value for ELSE you can also skip it

    count(case warning_level when 'VERBAL' then 1 end) as verbal_warnings,

    The following also works in MySQL, but I’m not sure if other systems will evaluate it the same way:

    count(warning_level = 'VERBAL' or null) as verbal_warnings,
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search