Combine records into one by specific columns not all , not Group By

[dlite922]

The problem i'm facing I have not come across on Google or don't know what it's called, so instead of explaining what i'm trying to do, showing you is a lot better.

I have a table that calculates hours for a specific activity.

activity---------type------------hours
AB123         NORMAL     8.3
AB123         BREAK       0.4
AB123         NORMAL     4.5
CD456         NORMAL     3.7
CD456         BREAK       2.1

What I want to do is group by the activity, BUT display the type on different columns like so (Wanted Output):

Activity       break         total
AB123        0.4            13.2   
CD456        2.1            5.8

The break is the sum of ALL TYPE = BREAK, while total is just SUM of all hours regardless of type.

Is this even possible without procedures?

Brain-locked here guys, appreciate any eye-openers,


Cheers,




Dan

[dlite922]

More INFO:

Here's the table:

 
 
SELECT activity, type, hours FROM timeSheet;
+----------+--------+-------+
| activity | type   | hours |
+----------+--------+-------+
| BGRBB    | NORMAL |   7.1 |
| CDRBB    | BREAK  |   1.2 |
| BGRBB    | BREAK  |   0.5 |
| CDRBB    | NORMAL |   5.9 |
+----------+--------+-------+
4 rows in set (0.00 sec)
 
 

Here's my effort:

 
SELECT 
activity, 
ROUND((SELECT SUM(hours) FROM timeSheet WHERE type = 'BREAK'),2) AS "Total Break", 
ROUND(SUM(hours),2) AS "Total Hours" 
FROM timeSheet 
GROUP BY activity;
+----------+-------------+-------------+
| activity | Total Break | Total Hours |
+----------+-------------+-------------+
| BGRBB    |        1.70 |        7.60 |
| CDRBB    |        1.70 |        7.10 |
+----------+-------------+-------------+
 
 

As you can see they both show 1.7 for break time although BGRBB should have only 0.5 break time and CDRBB should have 1.2 as the break time.

I guess I need a way to tell the inner select to grab the activity it should sum on.

I hope that helps further explain what I'm attempting to do.

Temporary tables?




Dan

[Atli]

You can use a Correlated Subquery.
That's basically a subquery that references the outer table.

Like:

SELECT 
    tsout.activity,
    (    SELECT SUM(tssub .`hours`) 
        FROM timeSheet AS tssub 
        WHERE tsout.activity = tssub.activity
        AND tssub.`type` = 'BREAK'
    ) AS 'Break',
    SUM(tsout.`hours`) AS 'Total'
FROM timeSheet AS tsout
GROUP BY tsout.activity
 

[dlite922]

You can use a Correlated Subquery.
That's basically a subquery that references the outer table.

Like:

SELECT 
    tsout.activity,
    (    SELECT SUM(tssub .`hours`) 
        FROM timeSheet AS tssub 
        WHERE tsout.activity = tssub.activity
        AND tssub.`type` = 'BREAK'
    ) AS 'Break',
    SUM(tsout.`hours`) AS 'Total'
FROM timeSheet AS tsout
GROUP BY tsout.activity
 

Thank you! I'll try this.


UPDATE: Worked like a charm!