# Thread: How to get a count of 0

1. Registered User
Join Date
Jun 2011
Posts
4

## 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. Registered User
Join Date
Jan 2003
Posts
4,310
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. Registered User
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.

4. Registered User
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
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. Registered User
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. Registered User
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. Registered User
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. Registered User
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
•