CODEWITHSUNDEEP
sqlcountdb2ibm-midrange
AJ Birone
AJ Birone

Reputation: 151

DB2 SQL Count across one to many relationship

I have two tables in a one to many relationship within an IBM DB2 database. I can't think of a way to describe what I'm doing in words easily so I'm linking an SQLFiddle.

Here is a working SQLFiddle on what I'm doing; Click Here

The SQLFiddle works exactly like I need it to. My problem is, I am using an IBM DB2 database and the COUNT function does not seem to work. Does anyone have a way to return what the SQLFiddle does in a IBM DB2 compatible way?

Here is the error I get from i Navigator; Click Here

Upvotes: 1

Views: 575

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You are aggregating by the wrong column. I think this is the query you want:

SELECT Table1.quote, COUNT(Table2.quote) as TotalItem
FROM Table1 LEFT JOIN
     Table2 
     ON Table1.quote = Table2.quote
GROUP BY Table1.quote
---------^

You need to aggregate by Table1.quote because Table2.quote might be NULL, because of the LEFT JOIN.

EDIT:

Your particular problem seems to be your having two tables with the same names. Just use column aliases:

SELECT t1.quote, COUNT(t2.quote) as TotalItem
FROM Table1 t1 LEFT JOIN
     Table2 t2
     ON t1.quote = t2.quote
GROUP BY t1.quote;

Upvotes: 1

Related Questions