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

In article <9dcedr$e55$1_at_redftp.redftp.attws.com>,  "Verna Legaspi" <verna.legaspi_at_attws.com> wrote:

> 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

SELECT SUM(CASE WHEN name LIKE '%Delete%' THEN 1 ELSE 0 END) AS Deleted,

   SUM(CASE WHEN name LIKE '%Inactiv%' THEN 1 ELSE 0 END) AS Inactivated,    SUM(CASE WHEN name LIKE '%Delete%' OR name LIKE '%Inactiv%'

            THEN 1 ELSE 0 END) AS Both
FROM kuaf
WHERE type = 0;

That's assuming a functional CASE statement. I don't doubt that you can trick something similar out with DECODE. Received on Thu May 10 2001 - 13:51:32 CEST