SUM

[beckyh]

Can I use this function around a field in my SQL statement. I can't find any mention of it in my DB2 SQL programming guide.

 

[beckyh]

http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.esqlc.doc/esqlc214.htm

appears to be according to this site...everyone agree? i just can't seem to get the sum function to work.

 

[gregsimpson]

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

 

[BrianTyler]

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

 

[beckyh]

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"

 

[BrianTyler]

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