Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SUM
- Thread starter beckyh
- Start date
- Thread starter
- #2
appears to be according to this site...everyone agree? i just can't seem to get the sum function to work.
gregsimpson
Programmer
- Apr 18, 2002
- 521
Beckyh,
the link you have is for Informix not DB2. However the syntax not surprisingly appears to be the same.
SELECT SUM(total_price) FROM items
WHERE order_num = 1002;
Might I suggest you post your SQL to the Forum?
Cheers
Greg
the link you have is for Informix not DB2. However the syntax not surprisingly appears to be the same.
SELECT SUM(total_price) FROM items
WHERE order_num = 1002;
Might I suggest you post your SQL to the Forum?
Cheers
Greg
BrianTyler
IS-IT--Management
Becky,
From your queries, I assume that you are fairly new to DB2 SQL. I, and probably most users of this forum, find that the best information to read is in the SQL cookbook by Graeme Birchall.
A Google search for 'Graeme Birchall' will find it.
Regards
Brian
From your queries, I assume that you are fairly new to DB2 SQL. I, and probably most users of this forum, find that the best information to read is in the SQL cookbook by Graeme Birchall.
A Google search for 'Graeme Birchall' will find it.
Regards
Brian
- Thread starter
- #5
Interestingly enough, I am not new to the SQL programming language. I am new to the DB2 syntax. When I tried adding a simple sum() function on a field, I get errors. Here it is....(I am having a hard time understanding the error handling that the ISeries returns.)
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0122 - Column BPKQCD or expression in SELECT list not valid.
SQL = "SELECT BPKQCD as Unit, BPKRCD as Nature, sum(BSBEVA) as Budget, sum(BPBAVA/100) as Actual, BPGDNB, sum((BSBEVA-(BPBAVA/100)))as Variance, AHADNA FROM YABPREP LEFT OUTER JOIN YABSREP ON BPKQCD = BSJ6CD AND BPKRCD = BSJ7CD and BPGDNB=BSNBCD INNER JOIN YAAHREP ON BPKRCD = AHAFCD"
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0122 - Column BPKQCD or expression in SELECT list not valid.
SQL = "SELECT BPKQCD as Unit, BPKRCD as Nature, sum(BSBEVA) as Budget, sum(BPBAVA/100) as Actual, BPGDNB, sum((BSBEVA-(BPBAVA/100)))as Variance, AHADNA FROM YABPREP LEFT OUTER JOIN YABSREP ON BPKQCD = BSJ6CD AND BPKRCD = BSJ7CD and BPGDNB=BSNBCD INNER JOIN YAAHREP ON BPKRCD = AHAFCD"
BrianTyler
IS-IT--Management
Becky,
I wasn't implying a lack of SQL knowledge, just the vaguaries/specifics for DB2.
Your latest problem is that, as soon as you include an aggragate function such as SUM or COUNT in a query, you must add a GROUP BY clause, containing all non-aggragate fields - in this case:
GROUP BY
BPKQCD,BPKRCD,BPGDNB,AHADNA
Brian
I wasn't implying a lack of SQL knowledge, just the vaguaries/specifics for DB2.
Your latest problem is that, as soon as you include an aggragate function such as SUM or COUNT in a query, you must add a GROUP BY clause, containing all non-aggragate fields - in this case:
GROUP BY
BPKQCD,BPKRCD,BPGDNB,AHADNA
Brian
- Status