Count(*)

[Anthony Robinson]

Is there any way to do multiple select count(*) statements in a SQL
query. I have a table that I want to get the counts of rows matching
certain criteria. For example:

SELECT COUNT(1) AS "UNFULFILLED REQUESTS" FROM AIMD.AIMQUERY WHERE
ERRORSEVERITY IS NOT NULL OR ERRORMESSAGE IS NOT NULL OR ERRORTYPE IS
NOT NULL AND (SELECT COUNT(*) AS "FULFILLED REQUESTS" FROM
AIMD.AIMQUERY WHERE ERRORSEVERITY IS NULL OR ERRORMESSAGE IS NULL OR
ERRORTYPE IS NULL)

I would like to do this in one statement if possible I figured I could
do it in a stored proc (create temp table and populate with the
counts).

Any insight would be appreciated.

Thanks

[Ian]

Anthony Robinson wrote:

Is there any way to do multiple select count(*) statements in a SQL
query. I have a table that I want to get the counts of rows matching
certain criteria. For example:

SELECT COUNT(1) AS "UNFULFILLED REQUESTS" FROM AIMD.AIMQUERY WHERE
ERRORSEVERITY IS NOT NULL OR ERRORMESSAGE IS NOT NULL OR ERRORTYPE IS
NOT NULL AND (SELECT COUNT(*) AS "FULFILLED REQUESTS" FROM
AIMD.AIMQUERY WHERE ERRORSEVERITY IS NULL OR ERRORMESSAGE IS NULL OR
ERRORTYPE IS NULL)

I would like to do this in one statement if possible I figured I could
do it in a stored proc (create temp table and populate with the
counts).

Any insight would be appreciated.

Here's how you would do this, based on the logic you gave above:

select
sum(case when (ERRORSEVERITY IS NOT NULL OR
ERRORMESSAGE IS NOT NULL OR
ERRORTYPE IS NOT NULL) then 1 else 0 end) as unfulfilled,
sum(case when (ERRORSEVERITY IS NULL OR
ERRORMESSAGE IS NULL OR
ERRORTYPE IS NULL) then 1 else 0 end) as fulfilled
from
aimd.aimquery;
Now, keep in mind that your query as written has a logic error (take a
where errorseverity = 1, errormessage = 'Error' and errortype is null:
both columns match, and the record counts as both fulfilled and unfulfilled).

Fulfilled is presumably where

ERRORSEVERITY IS NULL AND
ERRORMESSAGE IS NULL AND
ERRORTYPE IS NULL

but you should check this to make sure.

Good luck,

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Blair Adamache]

A UNION should work, although with no DDL or sample rows for
AIMD.AIMQUERY, I have had to invent some junk about the sample
database's EMPLOYEE table - no idea whether this logic is sound (it's
Friday afternoon after all):

SELECT 'UNFULFILLED REQUESTS:' as Status, COUNT(1) as Number
FROM employee
WHERE salary>500 OR Empno is not null OR workdept IS NOT NULL
union
SELECT 'FULFILLED REQUESTS:' as Status, COUNT(*) as Number
FROM employee
WHERE phoneno IS NULL OR hiredate IS NULL OR job IS NULL

STATUS NUMBER
--------------------- -----------
FULFILLED REQUESTS: 0
UNFULFILLED REQUESTS: 32

2 record(s) selected.

Anthony Robinson wrote:

Is there any way to do multiple select count(*) statements in a SQL
query. I have a table that I want to get the counts of rows matching
certain criteria. For example:

SELECT COUNT(1) AS "UNFULFILLED REQUESTS" FROM AIMD.AIMQUERY WHERE
ERRORSEVERITY IS NOT NULL OR ERRORMESSAGE IS NOT NULL OR ERRORTYPE IS
NOT NULL AND (SELECT COUNT(*) AS "FULFILLED REQUESTS" FROM
AIMD.AIMQUERY WHERE ERRORSEVERITY IS NULL OR ERRORMESSAGE IS NULL OR
ERRORTYPE IS NULL)

I would like to do this in one statement if possible I figured I could
do it in a stored proc (create temp table and populate with the
counts).

Any insight would be appreciated.

Thanks