SUM Problems in AS400

[edgarv]

edgarv (MIS) Sep 30, 2004
I was wondering if I could have a sql statement that does a SUM
I have tried
select FECHNB, FEABBJ, CUSNM, DDAITX, DDARQT01, DDARQT0101, INVAMT0201, sum(INVAMT0202)as INVAMT0202 From WEBQRY.ACCCOMB3
but then I have to add Group by and all the fields otherwise it does not work.

Is there a way to just have a sum or do I have to group by, and if I group by is there a way just to group by one of the fields?

 

[MarcLodge]

Edgarv,
What do you wish to see in the summed field? If each of the other fields contains differing values, at what level do require the summing to be performed?

In the example you have given, you would require a GROUP BY statement in order to calculate the result you are after. This can be bypassed slightly by having an innier and outer query.

Let us know if this helps.

Marc

 

[LarryKDB2]

Hi "edgarv",

As an example of GROUP BY, if you have a table employee_skills (empno, fname, lastname, skill_code, skill_date) then you might want to count the skills for each employee and show their names [even though empno would be a good enough "GROUP BY" column:

SELECT empno, fname, lastname, COUNT(skill_code) as Count_of_SKILLS
FROM employee_skills
GROUP BY empno, fname, lastname;

You could have a WHERE clause before the GROUP BY to choose a qualifying subset of rows to group.

There is a GROUP BY / COLUMN FUNCTION rule set that says if you don't use GROUP BY but DO have a column function, then all of the SELECT clause expressions must be functions or literals; or if you DO use GROUP BY then ALL of the SELECT clause expressions that are only column names [not column functions or literals] must be listed in the GROUP BY clause. In the example above, we wanted empno, fname, lastname in the SELECT without a function so all three had to be in the GROUP BY.

Sometimes we get around this restriction by saying:

SELECT empno, MIN(fname) AS fname, MIN(lastname) AS lastname,
COUNT(skill_code) as Count_of_SKILLS
FROM employee_skills
GROUP BY empno;

Larry Kintisch

http://www dot

DBIndexDesign dot com


IBM Contractor Instructor for DB2, SQl, QMF, Data modeling; [DB2 for z/OS]