Row Count using one SQL String [duplicate]

Question

I am pondering if there is away that I can count different values from different columns using one SQL Query.

I am making Graphs on my website and I want it to count No values in rows I was thinking to use the following

$sql="SELECT incident.ppe, incident.induction, incident.actions, incident.ssops 
                FROM incident WHERE ppe = 'No' OR induction = 'No' OR actions = 'No' OR ssops = 'No' 
                AND YEAR(date) = YEAR(CURDATE()) 
                AND client_id = '$slcustom1'"

The values are Yes and No Values. What my aim is is to echo the value of each Column in the graph values I am using.

var data = google.visualization.arrayToDataTable([
          ['', ''],
          ['PPE',     11], // Value 11 should be row counts of all the No's same with all values below
          ['Induction',      2],
          ['Actions',  2],
          ['SSOPs', 2]

Currently I do a SQL query for each value but this is making my code very long and untidy. Any suggestions would be appreciated. Group by will also work but how do I echo each result to the Graph value

The question linked as duplicate is about completely different problem. — Michal Hynčica
– Michal Hynčica, Commented Oct 21, 2019 at 19:17

Michal Hynčica · Accepted Answer · 2019-10-21 19:31:08Z

1

You can combine SUM() and IF() functions to count all 'No' in selected rows like this.

SELECT
  SUM(IF(incident.ppe = 'No', 1, 0)),
  SUM(IF(incident.induction = 'No', 1, 0)),
  SUM(IF(incident.actions = 'No', 1, 0)),
  SUM(IF(incident.ssops = 'No', 1, 0))
FROM incident

The IF() function will assign value 1 to the rows where the condition is true (where the attribute value is equal to 'No') and 0 to other rows. SUM() will then count a total of all rows. So if there are 5 'No' in ppe from a total of 9 rows the SUM() result will be 1+1+1+1+1+0+0+0+0=5

edited Oct 21, 2019 at 19:31

answered Oct 21, 2019 at 19:10

Michal Hynčica

6,2711 gold badge16 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

James Wright Over a year ago

Thanks, could you maybe explain how I would be able to echo the result to the graph values. Also please explain (incident.pp = 'No' , 1, 0) what would the 1 and 0 be

James Wright Over a year ago

Thanks your edit explanation makes sense. Now my final step is how do I pass that value to the var data in the graph

Collectives™ on Stack Overflow

Row Count using one SQL String [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related