Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: help with query totaling 1st and 2nd result columns

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

From: Daniel A. Morgan <dmorgan_at_exesolutions.com>
Date: Wed, 09 May 2001 22:32:55 -0700
Message-ID: <3AFA2807.44AFBF5F@exesolutions.com>

Verna Legaspi wrote:

> 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

You can add the third column by running the calculation and adding an alias. So for example I can create three different columns as:

SELECT count(*) FIRST, count(*) SECOND, count(*) THIRD FROM mytable;

And while I don't have time to explore it now ... I suspect your query could be done more cleanly using UNION.

Daniel A. Morgan Received on Thu May 10 2001 - 00:32:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US