| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
10-03-11, 13:38
|
|
Registered User
|
|
Join Date: Jun 2011
Posts: 4
|
|
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).
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!
|
|
10-03-11, 13:49
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
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
|
|
10-03-11, 14:15
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
|
|
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.
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
|
Last edited by Peter.Vanroose; 10-03-11 at 14:19.
|
10-03-11, 20:50
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
Well, unless the status doesn't occur (for instance none are above 2000).
|
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.
Quote:
|
is there a simple way to change the query to return 0's for those?
|
It is not necessary to change your query.
|
|
10-03-11, 21:10
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
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 21:14.
Reason: Replace whole sample query.
|
10-04-11, 00:53
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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.
|
|
10-05-11, 12:38
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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.
|
|
10-12-11, 08:18
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
How to get a count of 0
