Results 1 to 7 of 7

Thread: Count(*)

  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Count(*)

    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
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Lightbulb

    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    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?
    Last edited by ansonee; 01-23-04 at 14:40.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you are willing to get two rows back, you could do a UNION ALL.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    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

  6. #6
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525

    Re: Count(*)

    Originally posted by ansonee

    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).

    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;

  7. #7
    Join Date
    Jan 2004
    Location
    Kentucky
    Posts
    3

    Re: Count(*)

    Originally posted by ansonee
    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
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •