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

It might be desirable to have a column in the database whch indicates (Yes/No) if the request should be considered "fulfilled." If this is done, then the logic to make such determination occurs in only one place. Without it, many queries might have to have this logic, and the powers of the DBMS to efficiently locate and select data are generally being ignored.

If you had this field, the query would be a simple SELECT COUNT(*), IS_FULFILLED .. GROUP BY IS_FULFILLED.

Good solution. Unfortunately, our database is in a "code freeze" situation. I'm not allowed at this point to make any table changes....kinda stinks.

It would also involve rewriting some code - which again, is in a code freeze status.

Any other optiopns besides the cursor route?

If you are willing to get two rows back, you could do a UNION ALL.

Hi

What do you think about a CASE-expression? For instance:

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 REQUESTS", SUM(CASE WHEN ERRORSEVERITY IS NULL OR ERRORMESSAGE IS NULL OR ERRORTYPE IS NULL THEN 1 ELSE 0 END) AS "FULFILLED REQUESTS"
FROM AIMD.AIMQUERY

Sure you can, use a scalar sub-query! Pretty standard fair for retrieving mutiple totals in a single query. Apparently these aren't supported on all versions of DB2 which I find unbelievable!

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

Take a look at using
coalesce(sum(case errorsseverity when not null then 1 else null end), 0) as "unfulfilled Requests",

You might have to play with the syntax a bit, but I have used this multiple times when I needed to sum/count depending on an indicator field.