Nested Select Queries that Group By Similar Field

user_8102211 0
Viewed 18

I have a database of Time Entries which stores userID, roundedHours, dateSpent, and taskID for each entry (these are daily timecards). I am trying to create one query that will output sums of roundedHours grouped by userID where the taskID meets a specific value between specified dates.

The code I've attempted is below. I need it to be output and grouped by userID but instead, my output is the total sum for both overhead_admin and total_hours (same on each row) and not specific to the userID.

SELECT userName, userID, 
    (SELECT sum(roundedHours)
        FROM `db`.`TimeEntries`
        WHERE  taskId = 3050483 AND (spendDate BETWEEN '2020-11-02' AND '2020-11-08')) as overhead_admin,
    (SELECT sum(roundedHours)
        FROM `db`.`TimeEntries`
        WHERE (spendDate BETWEEN '2020-11-02' AND '2020-11-08')) as total_hours
FROM `db`.`TimeEntries`
GROUP BY userID

Can anyone help me so that each row will have the total SPECIFIC to that user and NOT summed as a whole? The output I'm getting that is wrong is shown in the image below:

enter image description here

mysql sql
1 Answers

Use conditional aggregation:

SELECT userName, userID, 
       sum(case when taskId = 3050483 AND (spendDate BETWEEN '2020-11-02' AND '2020-11-08' then roundedHours end) as overhead_admin,
       sum(case when spendDate BETWEEN '2020-11-02' AND '2020-11-08' then roundedHours end) as total_hours
FROM `db`.`TimeEntries`
GROUP BY userID
user_11440350
Related