Looking for correct query to pull certain totals-CodePudding

Here is my current query:

SELECT driver, callouts.id, max(date), count(*) as count FROM employees, callouts WHERE employees.id = callouts.id AND employees.status = 0 GROUP BY driver ORDER by driver

This gives me:

| DRIVER | ID | MAX(DATE)   | COUNT |
  BILL     2    2021-11-09    9
  FRED     8    2021-11-01    6
  TOM      4    2021-11-03    3

I'd like to add another column to my display table that shows the number of unexcused absences . In my table I have a column titled EXCUSED that has a 0 for excused and a 1 for unexcused. So, for instance, how would I add to this query to get the total number of each of these three guys unexcused absence. The COUNT column has the total number of instances each driver shows up which is essentially the total number of combined absences.

CodePudding user response：

In your case, as you're alrady grouping by driver, you can use sum(excused) and you will get the summed value of the excused column which gives you the total number of unexcused absences. SELECT driver, callouts.id, max(date), count(*) as count, sum(excused) as unexcused FROM employees, callouts WHERE employees.id = callouts.id AND employees.status = 0 GROUP BY driver ORDER by driver