Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Question Unanswered: How to get a count of 0

    Suppose I have a simple query something like this:

    Code:
    SELECT
      'Red' AS Status,
      Count(Usage) AS Count
    FROM by_day_water
    WHERE (Usage > 2000)
    UNION ALL
    SELECT
      'Yellow' AS Status,
      Count(Usage) AS Count
    FROM by_day_water
    WHERE (Usage > 1500) AND (Usage <= 2000)
    UNION ALL
    SELECT
      'Green' AS Status,
      Count(Usage) AS Count
    FROM by_day_water
    WHERE (Usage <= 1500)
    The query returns a count of each status.

    Code:
    Red 1
    Yellow 4
    Green 7
    Well, unless the status doesn't occur (for instance none are above 2000).

    Code:
    Yellow 5
    Green 7
    Whoops, what I want is

    Code:
    Red 0
    Yellow 5
    Green 7
    is there a simple way to change the query to return 0's for those? Thanks!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can try this:

    Code:
    SELECT
      'Red' AS Status, (select  Count(Usage) FROM by_day_water WHERE (Usage > 2000)) as count
    from sysibm.sysdummy1
    UNION ALL
    SELECT
      'Yellow' AS Status, (select   Count(Usage) FROM by_day_water WHERE (Usage > 1500) AND (Usage <= 2000)) as count
    from sysibm.sysdummy1
    UNION ALL
    SELECT
      'Green' AS Status, (select Count(Usage) FROM by_day_water WHERE (Usage <= 1500)) as count
    from sysibm.sysdummy1
    Andy

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Or the "outer join" way:
    Code:
    WITH codes(Status,c) AS (VALUES ('Red',1),('Yellow',2),('Green',3))
      , base_tbl(d) AS (
         SELECT CASE WHEN Usage > 2000 THEN 1
                     WHEN Usage > 1500 THEN 2 ELSE 3 END
         FROM by_day_water)
    SELECT Status, Count(d) AS Count
    FROM codes LEFT OUTER JOIN base_tbl ON c=d
    GROUP BY Status
    Additional advantage over the UNION ALL ways: the CASE...WHEN... avoid mistypings causing either overlapping ranges or non-covered ranges of "Usage" values.
    Last edited by Peter.Vanroose; 10-03-11 at 15:19.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Well, unless the status doesn't occur (for instance none are above 2000).

    Code:
    Yellow 5
    Green 7
    Did you tryed it by yourself?
    If you tried,
    what are DB2 version/release and platform(OS)?


    The result of my trial:
    Code:
    ----------------------------- Commands Entered ------------------------------
    connect to SAMPLE ;
    ------------------------------------------------------------------------------
    
       Database Connection Information
    
     Database server        = DB2/NT 9.7.4
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    A JDBC connection to the target has succeeded.
    ------------------------------ Commands Entered ------------------------------
    WITH
    by_day_water(usage) AS (
    VALUES
      1510 , 1520 , 1530 , 1540 , 1550
    , 1010 , 1020 , 1030 , 1040 , 1050 , 1060 , 1070
    )
    SELECT
      'Red' AS Status,
      Count(Usage) AS Count
    FROM by_day_water
    WHERE (Usage > 2000)
    UNION ALL
    SELECT
      'Yellow' AS Status,
      Count(Usage) AS Count
    FROM by_day_water
    WHERE (Usage > 1500) AND (Usage <= 2000)
    UNION ALL
    SELECT
      'Green' AS Status,
      Count(Usage) AS Count
    FROM by_day_water
    WHERE (Usage <= 1500)
    ;
    ------------------------------------------------------------------------------
    
    STATUS COUNT      
    ------ -----------
    Red              0
    Yellow           5
    Green            7
    
      3 record(s) selected.
    is there a simple way to change the query to return 0's for those?
    It is not necessary to change your query.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    WITH codes(Status,c) AS (VALUES ('Red',1),('Yellow',2),('Green',3))
      , base_tbl(d) AS (
         SELECT CASE WHEN Usage > 2000 THEN 1
                     WHEN Usage > 1500 THEN 2 ELSE 3 END
         FROM by_day_water)
    SELECT Status, Count(d) AS Count
    FROM codes LEFT OUTER JOIN base_tbl ON c=d
    GROUP BY Status
    It also can be written without using common-table-expression.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    by_day_water(usage) AS (
    VALUES
      1510 , 1520 , 1530 , 1540 , 1550
    , 1010 , 1020 , 1030 , 1040 , 1050 , 1060 , 1070
    )
    SELECT
           Status
         , Count(Usage) AS Count
      FROM (VALUES ('Red' , 1) , ('Yellow' , 2) , ('Green' , 3)
           ) c(status , order_by)
      LEFT OUTER JOIN
           by_day_water
       ON  CASE
           WHEN Usage >  2000 THEN
                'Red'
           WHEN Usage >  1500 THEN
                'Yellow'
           ELSE 'Green'
           END
           = Status
     GROUP BY
           Status
         , order_by
     ORDER BY
           order_by
    ;
    ------------------------------------------------------------------------------
    
    STATUS COUNT      
    ------ -----------
    Red              0
    Yellow           5
    Green            7
    
      3 record(s) selected.
    Last edited by tonkuma; 10-03-11 at 22:14. Reason: Replace whole sample query.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another shorter(but, a little tricky) example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    by_day_water(usage) AS (
    VALUES
         0 , 1020 , 1030 , 1040 , 1050 , 1499 , 1500
    , 1501 , 1520 , 1530 , 1540 , 2000
    )
    SELECT SUBSTR('Green YellowRed   ' , 7 + 6 * rank , 6) AS Status
         , Count(Usage) AS Count
      FROM (VALUES -1 , 0 , 1) p(rank)
      LEFT OUTER JOIN
           by_day_water
       ON  SIGN( (Usage - 1) / 500 - 3 ) = rank
     GROUP BY
           rank
     ORDER BY
           rank DESC
    ;
    ------------------------------------------------------------------------------
    
    STATUS COUNT      
    ------ -----------
    Red              0
    Yellow           5
    Green            7
    
      3 record(s) selected.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I tried to make the query short for fun.

    The example might not be practical. Because, it's a little hard to understand the rationale.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    by_day_water(usage) AS (
    VALUES
         0 , 1020 , 1030 , 1040 , 1050 , 1499 , 1500
    , 1501 , 1520 , 1530 , 1540 , 2000
    )
    SELECT SUBSTR('Red    Yellow Green ' , rnk2 , 6) AS Status
         , SUM( MOD( rnk2 - SIGN( 3 - (Usage - 1) / 500 ) , 3 ) / 2 ) AS Count
     FROM  (VALUES 1 , 8 , 15 ) p(rnk2)
         , by_day_water
     GROUP BY
           rnk2
    ;
    ------------------------------------------------------------------------------
    
    STATUS COUNT      
    ------ -----------
    Red              0
    Yellow           5
    Green            7
    
      3 record(s) selected.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another trick...
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    by_day_water(usage) AS (
    VALUES
         0 , 1020 , 1030 , 1040 , 1050 , 1499 , 1500
    , 1501 , 1520 , 1530 , 1540 , 2000
    )
    SELECT SUBSTR('Red   YellowGreen ' , 7 + rank * 6 , 6) AS Status
         , SUM( INT( COS( rank - SIGN( 3 - (Usage - 1) / 500 ) ) ) ) AS Count
     FROM  (VALUES -1 , 0 , 1 ) p(rank)
         , by_day_water
     GROUP BY
           rank
    ;
    ------------------------------------------------------------------------------
    
    STATUS COUNT      
    ------ -----------
    Red              0
    Yellow           5
    Green            7
    
      3 record(s) selected.
    Where INT( COS( x ) ) returns 1 if x = 0, otherwise returns 0. (x is INTEGER)
    then,
    Code:
         , SUM( INT( COS( rank - SIGN( 3 - (Usage - 1) / 500 ) ) ) ) AS Count
    is euivalent to
    Code:
                CASE rank WHEN SIGN( 3 - (Usage - 1) / 500 ) THEN 1 ELSE 0 END

Posting Permissions

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