CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
aantiix
aantiix

Reputation: 534

Union in outer query

I'm attempting to combine multiple rows using a UNION but I need to pull in additional data as well. My thought was to use a UNION in the outer query but I can't seem to make it work. Or am I going about this all wrong?

The data I have is like this:

+------+------+-------+---------+---------+
|  ID  | Time | Total | Weekday | Weekend |
+------+------+-------+---------+---------+
| 1001 | AM   |     5 |       5 |       0 |
| 1001 | AM   |     2 |       0 |       2 |
| 1001 | AM   |     4 |       1 |       3 |
| 1001 | AM   |     5 |       3 |       2 |
| 1001 | PM   |     5 |       3 |       2 |
| 1001 | PM   |     5 |       5 |       0 |
| 1002 | PM   |     4 |       2 |       2 |
| 1002 | PM   |     3 |       3 |       0 |
| 1002 | PM   |     1 |       0 |       1 |
+------+------+-------+---------+---------+

What I want to see is like this:

+------+---------+------+-------+
|  ID  | DayType | Time | Tasks |
+------+---------+------+-------+
| 1001 | Weekday | AM   |     9 |
| 1001 | Weekend | AM   |     7 |
| 1001 | Weekday | PM   |     8 |
| 1001 | Weekend | PM   |     2 |
| 1002 | Weekday | PM   |     5 |
| 1002 | Weekend | PM   |     3 |
+------+---------+------+-------+

The closest I've come so far is using UNION statement like the following:

SELECT * FROM
(
    SELECT Weekday, 'Weekday' as 'DayType' FROM t1
    UNION
    SELECT Weekend, 'Weekend' as 'DayType' FROM t1
) AS X

Which results in something like the following:

+---------+---------+
| Weekday | DayType |
+---------+---------+
|       2 | Weekend |
|       0 | Weekday |
|       2 | Weekday |
|       0 | Weekend |
|      10 | Weekday |
+---------+---------+

I don't see any rhyme or reason as to what the numbers are under the 'Weekday' column, I suspect they're being grouped somehow. And of course there are several other columns missing, but since I can't put a large scope in the outer query with this as inner one, I can't figure out how to pull those in. Help is greatly appreciated.

Upvotes: 0

Views: 57

Answers (3)

Mprante
Mprante

Reputation: 21

Not tested, but it should do the trick. It may require 2 proc sql steps for the calculation, one for summing and one for the case when statements. If you have extra lines, just use a max statement and group by ID, Time, type_day. 

Proc sql; create table want as select ID, Time, 
    sum(weekday) as weekdayTask,
    sum(weekend) as weekendTask,
    case when calculated weekdaytask>0 then weekdaytask
        when calculated weekendtask>0 then weekendtask else .
        end as Task,
    case when calculated weekdaytask>0 then "Weekday"
        when calculated weekendtask>0 then "Weekend" 
        end as Day_Type 
from have
group by ID, Time
;quit;

Proc sql; create table want2 as select ID, Time, Day_Type, Task
from want
;quit;

Upvotes: 0

Stefano Zanini
Stefano Zanini

Reputation: 5916

Try with this

select  ID, 'Weekday' as DayType, Time, sum(Weekday)
from    t1
group by ID, Time
union all
select  ID, 'Weekend', Time, sum(Weekend)
from    t1
group by ID, Time
order by order by 1, 3, 2

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

It looks like you want to union all a pair of aggregation queries that use sum() and group by id, time, one for Weekday and one for Weekend:

select Id, DayType = 'Weekend', [time], Tasks=sum(Weekend)
from t
group by id, [time]
union all
select Id, DayType = 'Weekday', [time], Tasks=sum(Weekday)
from t
group by id, [time]

Upvotes: 2

Related Questions