| View previous topic :: View next topic |
| Author |
Message |
Nancy
Beginner
Joined: 23 May 2003
Posts: 77
Topics: 26
|
 Posted: Mon Apr 24, 2006 4:14 am Post subject: SUM and REPLACE |
 |
|
Hello,
Requirement is like that I need to use SUM and REPLACE in a single query. SUM is for summation of few of the items and REPLACE to use if the value is NULL then should be replaced by SPACES / ZEROS in the output report. Please suggest a way to do that.
If we can't do that in a single query then can we use the temp tables where we can put the output data of first query and then do a REPLACE but that would not be efficient I guess.
Cheers
Nancy |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12369
Topics: 75
Location: San Jose
|
| Posted: Mon Apr 24, 2006 4:54 am Post subject: |
|
|
| Quote: |
SUM is for summation of few of the items and REPLACE to use if the value is NULL then should be replaced by SPACES / ZEROS in the output report
|
Nancy,
You say REPLACE and output report? Do you want to UPDATE the null value columns to zero's or You just want to show zero's in the report for null values without updating the DB2 Table?
Also it would help if you provide sample data.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Nancy
Beginner
Joined: 23 May 2003
Posts: 77
Topics: 26
|
| Posted: Mon Apr 24, 2006 5:04 am Post subject: |
|
|
Kolusu,
I just want to show the values as ZERO in the output report, don't want to do any updates on DB2 table. My output report is looking like -
| Code: |
4 CUSTOMERS WITH 3 ACCOUNTS 6
5 CUSTOMERS WITH 4 ACCOUNTS 1
6 CUSTOMERS WITH 5 ACCOUNTS 1
7 CUSTOMERS WITH 6 ACCOUNTS 1
8 CUSTOMERS WITH 7 ACCOUNTS -----------
9 CUSTOMERS WITH 8 ACCOUNTS 1
10 CUSTOMERS WITH 9 ACCOUNTS -----------
11 CUSTOMERS WITH 10 ACCOUNTS -----------
12 CUSTOMERS WITH 11-20 ACCOUNTS -----------
13 CUSTOMERS WITH > 20 ACCOUNTS 2
14 AVERAGE ACCOUNTS PER CUSTOMER 5
I need to remove these NULLs and replace them by ZEROs in the query itself where I am doing the summation.
SELECT
4 AS NO_OF_ACCTS,
'CUSTOMERS WITH 3 ACCOUNTS',
SUM(S.NO_CUST)
FROM (
SELECT
R.NO_OF_ACC AS NO_OF_ACCTS,
COUNT(*) AS NO_CUST
FROM (
SELECT COUNT(*) AS NO_OF_ACC,
A.CUST_ID AS D
FROM SIBU001.SIB_USER_DETL A,
SIBU001.SIB_USER_ACCT_DETL B
WHERE A.CUST_ID =
B.CUST_ID
AND A.USER_TYP='P'
AND SUBSTR(B.PAYEE_ACNO,15,2)=' '
GROUP BY A.CUST_ID) R
GROUP BY R.NO_OF_ACC) S
WHERE S.NO_OF_ACCTS =3
;
|
Also I am doing UNION of all the queries to get the CUSTOMERS HAVING N ACCOUNTS where N is 4,5,6,7 etc. I know this is very inefficient and we can use a procedure for that. Can you suggest something on that please ?
Thanks
Nancy |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12369
Topics: 75
Location: San Jose
|
| Posted: Mon Apr 24, 2006 5:08 am Post subject: |
|
|
Nancy,
I think there is a better way of doing it. Can you actually list the requirement? Give the list of the tables involved and a sample data for each table.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Nancy
Beginner
Joined: 23 May 2003
Posts: 77
Topics: 26
|
| Posted: Mon Apr 24, 2006 5:22 am Post subject: |
|
|
Ok, let me make myself clear in that.
The output Report should look like -
Number of customers registered with cards Z,ZZZ,ZZ9
Customers with 1 account Z,ZZZ,ZZ9
Customers with 2 account Z,ZZZ,ZZ9
(3, 4, 5, 6 ....9)
Customers with 10 account Z,ZZZ,ZZ9
Customers with 11-20 account Z,ZZZ,ZZ9
Customers with >20 account Z,ZZZ,ZZ9
Average number of accounts Z,ZZZ,ZZ9
For this we have two tables SIB_USER_DETL (holds primary and secondary users) and SIB_USER_ACT_DETL (holds the details of user accounts). We are writing the query for each number of account like I pasted above and then doing a UNION on all those queries but that is not an efficient way. Also we are getting NULLs in the output report that should be displayed as ZEROs.
Hope it helps !
Thanks
nancy |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12369
Topics: 75
Location: San Jose
|
| Posted: Mon Apr 24, 2006 8:12 am Post subject: |
|
|
Nancy,
Try this sql
| Code: |
SELECT Y.ACT_DESC
,SUM(ACT_CNT)
FROM (SELECT CASE
WHEN X.CNT = 1 THEN CHAR('CUSTOMERS WITH 01 ACCOUNTS')
WHEN X.CNT = 2 THEN CHAR('CUSTOMERS WITH 02 ACCOUNTS')
WHEN X.CNT = 3 THEN CHAR('CUSTOMERS WITH 03 ACCOUNTS')
WHEN X.CNT = 4 THEN CHAR('CUSTOMERS WITH 04 ACCOUNTS')
WHEN X.CNT = 5 THEN CHAR('CUSTOMERS WITH 05 ACCOUNTS')
WHEN X.CNT = 6 THEN CHAR('CUSTOMERS WITH 06 ACCOUNTS')
WHEN X.CNT = 7 THEN CHAR('CUSTOMERS WITH 07 ACCOUNTS')
WHEN X.CNT = 8 THEN CHAR('CUSTOMERS WITH 08 ACCOUNTS')
WHEN X.CNT = 9 THEN CHAR('CUSTOMERS WITH 09 ACCOUNTS')
WHEN X.CNT = 10 THEN CHAR('CUSTOMERS WITH 10 ACCOUNTS')
WHEN X.CNT > 10 AND X.CNT <20> 20 THEN
CHAR('CUSTOMERS WITH 20> ACCOUNTS')
END AS ACT_DESC
,COUNT(X.CNT) AS ACT_CNT
FROM (SELECT COUNT(*) CNT
,A.CUST_ID
FROM SIBU001.SIB_USER_DETL A
,SIBU001.SIB_USER_ACCT_DETL B
WHERE A.CUST_ID = B.CUST_ID
AND A.USER_TYP = 'P'
AND SUBSTR(B.PAYEE_ACNO,15,2) = ' '
GROUP BY A.CUST_ID) X
GROUP BY X.CNT) Y
GROUP BY Y.ACT_DESC
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Nancy
Beginner
Joined: 23 May 2003
Posts: 77
Topics: 26
|
| Posted: Mon Apr 24, 2006 8:47 am Post subject: |
|
|
Kolusu,
No doubt it is looking much better now. Thanks for the help !
The only problem I could see is with the display of NULL values. If the SUM is NULL, then it should be displayed as ZEROs in the output report but it is not doing that right now.
Regards
Nancy |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12369
Topics: 75
Location: San Jose
|
| Posted: Mon Apr 24, 2006 8:59 am Post subject: |
|
|
nancy,
Why would you even get null values? we are writing out records only when there is match on both tables. I don't see as to how you can get the null values from the sum. The sql posted by me will never give you null values
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Nancy
Beginner
Joined: 23 May 2003
Posts: 77
Topics: 26
|
| Posted: Mon Apr 24, 2006 10:03 am Post subject: |
|
|
No, actually I wanted a report displaying all the values even if there is not any match but report should be printed to have the heading so I slightly modified the SQL. But couldnt get rid of NULLs 
Nancy |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12369
Topics: 75
Location: San Jose
|
| Posted: Mon Apr 24, 2006 10:17 am Post subject: |
|
|
Nancy,
If that is case then you can add a CASE statement for the sum also.
| Code: |
CASE WHEN SUM(Y.ACT_CNT) IS NULL THEN 0
ELSE SUM(Y.ACT_CNT)
END
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Nancy
Beginner
Joined: 23 May 2003
Posts: 77
Topics: 26
|
| Posted: Mon Apr 24, 2006 10:33 am Post subject: |
|
|
Kolusu,
This is exactly what I was looking for. Got the expected result now in a better way. Your help is always appreciated.
Cheers
Nancy |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
