Re: Combining two SQL Statements

Michael wrote:
> Hello,
> I need to combine this two SQL-Statements to one SQL:
>
> 1. select UserId, UsedSlots from user_used_slots;
>
> UserId UsedSlots
> a 3
> b 5
> a 4
> c 3
> b 5
>
> 2. select UserId, FreeSlots from user_free_slots;
>
> UserId FreeSlots
> a 4
> b 5
> b 3
> c 3
> b 5
>
> ==> The resulting SQL-Staetement should show something like this:
> (TotalSlots = UsedSlots + FreeSlots)
>
> UserId UsedSlots FreeSlots TotalSlots
> a 7 4 11
> b 10 15 25
> c 3 3 6
>
> How can I produce a Output which calculates everything correctly??
> Thanks,
> Michael

I get:

select u1,sum(su),sum(sf),sum(su)+sum(sf) from (
select userid u1,sum(usedslots) su,0 sf from used group by userid union all
select userid u2,0 su,sum(freeslots) sf from free group by userid
) group by u1;

Seems a bit kludgy though. Is there a better way?

Dave. Received on Thu Oct 07 2004 - 06:12:05 CDT