Calculating SUM() results from subqueries into a total

Question

My brain is starting to hurt with this query and I would appreciate some guidance. What I am trying to get as the result of this query are three values: attendanceScore, lootScore, and totalScore (attendanceScore - lootScore).

• Attendance is tracked across three tables: attendance, attendancelog, and attendancevalues.

  • attendance records an individual's attendance status attached to an attendancelog record. There are types of attendance such as "attended", "missed", and "called out".
  • attendancelog is the parent record which records the event type, title and date as well as who logged the attendance and when.
  • attendancevalues is a config table that matches the attendance type from attendance and the event type from attendancelog and returns a configurable value FLOAT.

• Loot is tracked across two tables: loot and loottypes.

  • loot logs each individual item, who received it and when and what type of loot it was (Primary, Secondary, Free-for-all).
  • loottypes is a config table that takes the type from loot and returns a configurable cost FLOAT.

---

After some work I have come up with a working query to get attendanceScore and lootScore:

SELECT 
(SELECT SUM(t3.`value`)
FROM `attendance` t1
    INNER JOIN `attendancelog` t2
        ON t2.`id` = t1.`attendancelog_id`
    INNER JOIN `attendancevalues` t3
        ON t3.`eventtype_id` = t2.`type` AND t3.`attendancetype_id` = t1.`type`
WHERE t1.`user_id` = 3) as attendanceScore,

(SELECT SUM(t2.`cost`) 
    FROM `loot` t1
        INNER JOIN `loottypes` t2
            ON t2.`id` = t1.`type`
    WHERE t1.`user_id` = 3) as lootScore

I know this doesn't work, but I tried to add (attendanceScore - lootScore) to the query but it says those fields are not available. That is ultimately what I need to complete the query.

I can get the result I want by copying each of the subqueries directly into (attendanceScore - lootScore) but it is just absolutely hideous and I'm sure unnecessary:

SELECT 
(SELECT SUM(t3.`value`)
FROM `attendance` t1
    INNER JOIN `attendancelog` t2
        ON t2.`id` = t1.`attendancelog_id`
    INNER JOIN `attendancevalues` t3
        ON t3.`eventtype_id` = t2.`type` AND t3.`attendancetype_id` = t1.`type`
WHERE t1.`user_id` = 3) as attendanceScore,

(SELECT SUM(t2.`cost`) 
    FROM `loot` t1
        INNER JOIN `loottypes` t2
            ON t2.`id` = t1.`type`
    WHERE t1.`user_id` = 3) as lootScore,

(
    (SELECT SUM(t3.`value`)
    FROM `attendance` t1
        INNER JOIN `attendancelog` t2
            ON t2.`id` = t1.`attendancelog_id`
        INNER JOIN `attendancevalues` t3
            ON t3.`eventtype_id` = t2.`type` AND t3.`attendancetype_id` = t1.`type`
    WHERE t1.`user_id` = 3) - (SELECT SUM(t2.`cost`) 
    FROM `loot` t1
        INNER JOIN `loottypes` t2
            ON t2.`id` = t1.`type`
    WHERE t1.`user_id` = 3)
) as totalScore

Could someone help me understand what methods to use for cleaning this up into something more streamlined and efficient?

Answer 1

You may use an inline view

SELECT attendanceScore,
        lootScore,
        attendanceScore - lootScore as totalScore
FROM
(
    SELECT 
    (
        SELECT SUM(t3.`value`)
        FROM `attendance` t1
        INNER JOIN `attendancelog` t2
            ON t2.`id` = t1.`attendancelog_id`
        INNER JOIN `attendancevalues` t3
            ON t3.`eventtype_id` = t2.`type` AND t3.`attendancetype_id` = t1.`type`
        WHERE t1.`user_id` = 3
    ) as attendanceScore,
    (
        SELECT SUM(t2.`cost`) 
        FROM `loot` t1
        INNER JOIN `loottypes` t2 ON t2.`id` = t1.`type`
        WHERE t1.`user_id` = 3) as lootScore
) t