Condensing a query into a single better formatted query

I have been trying to combine four separate queries into one query without success. what I have:

   'SELECT SUM(students.Earned) AS Theory, QuizTitle from students WHERE students.QuizTitle LIKE 'THEORY%' and students.DriverLicense = 'D120001102' 
UNION SELECT SUM(students.Earned) AS Hazmat,QuizTitle from students WHERE students.QuizTitle LIKE 'HAZMAT%' and students.DriverLicense = 'D120001102'
UNION SELECT SUM(students.Earned) AS Pass, QuizTitle from students WHERE students.QuizTitle LIKE 'PASS%' and students.DriverLicense = 'D120001102'
UNION SELECT SUM(students.Earned) AS Bus, QuizTitle from students WHERE students.QuizTitle LIKE 'SCHOOL%' and students.DriverLicense = 'D120001102'
group by DriverLicense'

How can I condense this into a better formatted query but get the same results??

Solution

This query does not give exactly the same result, but your query produces invalid results. I think this gives the equivalent information you need:

SELECT
  SUM(CASE WHEN QuizTitle LIKE 'THEORY%' THEN students.Earned END) AS Theory,
  SUM(CASE WHEN QuizTitle LIKE 'HAZMAT%' THEN students.Earned END) AS Hazmat,
  SUM(CASE WHEN QuizTitle LIKE 'PASS%' THEN students.Earned END) AS Pass,
  SUM(CASE WHEN QuizTitle LIKE 'SCHOOL%' THEN students.Earned END) AS Bus
FROM students
WHERE DriverLicense = 'D120001102';

The CASE expressions return NULL if the condition is not satisfied. The SUM() ignores rows where the expression is NULL. So these aggregations effectively sum disjoint subset of rows.

You can't have QuizTitle in the results, because that value may be variable, and that violates the single-value rule of aggregation. See my answer here: https://stackoverflow.com/a/13999903/20860 for an explanation.

No need for GROUP BY since you are searching for a single value of DriverLicense, and by definition there will be one group. That's the default for a query with aggregation when you omit the GROUP BY.

If you want distinct column labels for each sum, then they must be in separate columns, not in a single column of a series of unioned queries. When you use UNION, the first query determines the column names. The column names of subsequent queries are ignored. A column can only have one heading.

Re your comment:

I can't be sure because I don't know the structure of your table, but this may work:

SELECT DriverLicense,
  SUM(CASE WHEN QuizTitle LIKE 'THEORY%' THEN students.Earned END) AS Theory,
  SUM(CASE WHEN QuizTitle LIKE 'HAZMAT%' THEN students.Earned END) AS Hazmat,
  SUM(CASE WHEN QuizTitle LIKE 'PASS%' THEN students.Earned END) AS Pass,
  SUM(CASE WHEN QuizTitle LIKE 'SCHOOL%' THEN students.Earned END) AS Bus
FROM students
GROUP BY DriverLicense;