0

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

James Wright
  • 143
  • 10

1 Answers1

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

Michal Hynčica
  • 5,038
  • 1
  • 12
  • 24
  • 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 Oct 21 '19 at 19:21
  • Thanks your edit explanation makes sense. Now my final step is how do I pass that value to the var data in the graph – James Wright Oct 21 '19 at 19:43