CODEWITHSUNDEEP
sqljoindb2sum
Kenshin
Kenshin

Reputation: 111

DB2 How do you use SUM function with Join

DB2 info: z/OS v9.1

I am trying to create a query that selects different columns from different tables and one of the tables column's I need the sum of. For example:

    SELECT A.column1, B.*, SUM(C.Colum3) as col3
    FROM schema.TableA A, schema.TableB B, schema.TableC C
    WHERE A.column2 = B.column2 AND A.column1 = C.column1 
    GROUP BY A.column1;

The query doesn't work. I get an error: 

    COLUMN OR EXPRESSION IN THE SELECT LIST IS NOT VALID.

I have tried different query examples like this one How can I sum a group of sums? SQL Server 2008

It is in sql server so that could be why it doesn't work. Not a lot of examples out there for DB2. :(

Upvotes: 0

Views: 6057

Answers (1)

Clockwork-Muse
Clockwork-Muse

Reputation: 13076

Because you'll get the same error in SQL Server, and every other major RDBMS too (excluding MySQL's quirk here): you have columns that aren't included in the GROUP BY clause, or part of an aggregate, so the system doesn't know what to do with them (MySQL returns a random row for those columns, I think, probably not what you want).

It's unclear what you're trying to get 'sum of sums' of, since you only list the one SUM(). Assuming that TableC is the only table you need to aggregate, use a Common Table Expression (CTE), like so: 

WITH Summed_TableC (column1, summedColumn) as (SELECT column1, SUM(column3)
                                               FROM TableC
                                               GROUP BY column1)
SELECT a.Column1, b.*, c.summedColumn
FROM schema.TableA as a
JOIN schema.TableB as b
ON b.column2 = a.column2
JOIN Summed_TableC as c
ON c.column1 = a.column1

Oh - never use the implicit join syntax. You've nicely qualified which tables which columns come from, but if you don't, it's just plain confusing. Avoid that by explicitly JOINing the tables, and their columns.

Upvotes: 1

Related Questions