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

"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 - 00:38:03 CDT