Re: help with query totaling 1st and 2nd result columns

Try this:

select inactivated, deleted, inactivated+deleted total from (select sum(decode(name,'Inactive',1,0)) inactivated, sum(decode(name,'Deleted',1,0)) deleted
from kuaf where type = 0);

In article <tfka5rrm6oklae_at_beta-news.demon.nl>, Sybrand Bakker says...
>
>
>"Verna Legaspi" <verna.legaspi_at_attws.com> wrote in message
>news:9dcedr$e55$1_at_redftp.redftp.attws.com...
>> Hello,
>>
>> Sorry for the duplicate postings... not sure which group to post to. I
 have
>> a fairly simple? enough concept that I'm trying to implement on a query.
>>
>> Basically we have a users table which has deleted and inactivate users.
 I'd
>> like my query to show the total (count) of deleted and inactive users. I
>> would also like the query to give me to total of both deleted and inactive
>> on the 3rd column.
>>
>> So far, here's what I got:
>> SELECT (SELECT COUNT(1)
>> FROM kuaf
>> WHERE type = 0
>> AND name LIKE '%Delete%') "Deleted",
>> (SELECT COUNT(1)
>> FROM kuaf
>> WHERE type = 0
>> AND name LIKE '%Inactiv%') "Inactivated"
>> FROM dual
>>
>> My results looks like this:
>> Deleted Inactivated
>> ---------- -----------
>> 1180 162
>>
>> Does anyone know how I can add a third column to give me the total of both
>> columns' counts/totals? I realize my SQL may be inefficient, and I could
>> probably make it more inefficient by making a 3rd sub-query that just does
 a
>> count of both deleted and inactive. But now I'm really more curious
 rather
>> than anything else.
>>
>> TIA,
>> Verna
>>
>>
>>
>
>
> SELECT deleted, inactivated, deleted+inactivated
>from
>(
>SELECT COUNT(1) deleted
>FROM kuaf
>WHERE type = 0
>AND name LIKE '%Delete%')
>,
>(SELECT COUNT(1) "Inactivated"
> FROM kuaf
> WHERE type = 0
> AND name LIKE '%Inactiv%'
>)
>
>
>But then of course, if your name column would have only two values you could
>simply do
>
>select sum(decode(name,'Inactive',1,0)), sum(decode(name,'Deleted',1,0)
>from kuaf
>where type = 0;
>
>Looks like you are making life difficult or suffer from bad design.
>
>Hth,
>
>Sybrand Bakker, Oracle DBA
>
>
>
>
Received on Thu May 10 2001 - 10:17:59 CDT