sum of accounts with no entries.

#1

Hi,

I have two tables, Account and Logs.
Log as an "account" column linked to the "Account.id" column, and an
"amount"

I want for each Account, the sum of all amounts.

I used a simple query:
SELECT
Accounts.name ,
sum(Logs.amount) AS balance
FROM Accounts, Logs
WHERE Accounts.id=Logs.account
GROUP BY Accounts.id

But that does not do exactly do what I want because it does not
include the Accounts that are never referenced in the Log table. ( I
would like them to come out with either a 0 or NULL sum)

I thought about adding a dummy row in the Log for each Account (with a
0 amount) to make it do what i want, but I wondered if there was
another way ?

Thanks

#2

Replying to myself...

On Mar 1, 4:08*pm, Fabrice <fabrice.gaut... (AT) gmail (DOT) com> wrote:

Quote:

[...]
I want for each Account, the sum of all amounts.

I used a simple query:
* * * * *SELECT
* * * * *Accounts.name ,
* * * * *sum(Logs.amount) AS balance
* * * * *FROM Accounts, Logs
* * * * *WHERE Accounts.id=Logs.account
* * * * *GROUP BY Accounts.id

But that does not do exactly do what I want because it does not
include the Accounts that are never referenced in the Log table. ( I
would like them to come out with either a 0 or NULL sum)

This query:

SELECT Accounts.id, Accounts.name, sum(Logs.amount) FROM Accounts
LEFT OUTER JOIN Logs ON Accounts.id=Logs.account GROUP BY id;

is better in that it gives me a NULL sum.

Now if I could get a 0 instead of NULL that would be perfect.

I figured out how to use UNION to add a row with a 0 amount for each
account, but I could not find a way to do the aggregate function on
the results of the UNION...

#3

On Mar 1, 8:29*pm, Fabrice <fabrice.gaut... (AT) gmail (DOT) com> wrote:

Quote:

Replying to myself...

On Mar 1, 4:08*pm, Fabrice <fabrice.gaut... (AT) gmail (DOT) com> wrote:

[...]
I want for each Account, the sum of all amounts.

I used a simple query:
* * * * *SELECT
* * * * *Accounts.name ,
* * * * *sum(Logs.amount) AS balance
* * * * *FROM Accounts, Logs
* * * * *WHERE Accounts.id=Logs.account
* * * * *GROUP BY Accounts.id

But that does not do exactly do what I want because it does not
include the Accounts that are never referenced in the Log table. ( I
would like them to come out with either a 0 or NULL sum)

This query:

SELECT Accounts.id, Accounts.name, sum(Logs.amount) *FROM Accounts
LEFT OUTER JOIN Logs ON Accounts.id=Logs.account GROUP BY id;

is better in that it gives me a NULL sum.

Now if I could get a 0 instead of NULL that would be perfect.

I figured out how to use UNION to add a row with a 0 amount for each
account, but I could not find a way to do the aggregate function on
the results of the UNION...

In Oracle you would use the NVL() function.
SELECT Accounts.id, Accounts.name, sum(NVL(Logs.amount,0)) FROM
Accounts
LEFT OUTER JOIN Logs ON Accounts.id=Logs.account GROUP BY id;

But since you didn't bother to mention what DBMS you use, that may be
the wrong answer.

I tried, but my ESP failed due to the headache I have.
Ed

#4

On 02.03.2009 03:02, Ed Prochak wrote:

Quote:

On Mar 1, 8:29 pm, Fabrice <fabrice.gaut... (AT) gmail (DOT) com> wrote:
Replying to myself...

On Mar 1, 4:08 pm, Fabrice <fabrice.gaut... (AT) gmail (DOT) com> wrote:

[...]
I want for each Account, the sum of all amounts.
I used a simple query:
SELECT
Accounts.name ,
sum(Logs.amount) AS balance
FROM Accounts, Logs
WHERE Accounts.id=Logs.account
GROUP BY Accounts.id
But that does not do exactly do what I want because it does not
include the Accounts that are never referenced in the Log table. ( I
would like them to come out with either a 0 or NULL sum)
This query:

SELECT Accounts.id, Accounts.name, sum(Logs.amount) FROM Accounts
LEFT OUTER JOIN Logs ON Accounts.id=Logs.account GROUP BY id;

is better in that it gives me a NULL sum.

Now if I could get a 0 instead of NULL that would be perfect.

I figured out how to use UNION to add a row with a 0 amount for each
account, but I could not find a way to do the aggregate function on
the results of the UNION...

In Oracle you would use the NVL() function.
SELECT Accounts.id, Accounts.name, sum(NVL(Logs.amount,0)) FROM
Accounts
LEFT OUTER JOIN Logs ON Accounts.id=Logs.account GROUP BY id;

But since you didn't bother to mention what DBMS you use, that may be
the wrong answer.

For other RDBMS use CASE.

robert

#5

Robert Klemme <shortcutter (AT) googlemail (DOT) com> wrote in news:711iouFirl33U1
@mid.individual.net:

Quote:

On 02.03.2009 03:02, Ed Prochak wrote:
On Mar 1, 8:29 pm, Fabrice <fabrice.gaut... (AT) gmail (DOT) com> wrote:
In Oracle you would use the NVL() function.
SELECT Accounts.id, Accounts.name, sum(NVL(Logs.amount,0)) FROM
Accounts
LEFT OUTER JOIN Logs ON Accounts.id=Logs.account GROUP BY id;

But since you didn't bother to mention what DBMS you use, that may be
the wrong answer.

For other RDBMS use CASE.

robert

And COALESCE is an ANSI/ISO SQL short form for CASE.
E.g. COALESCE(Logs.amount,0)

COALESCE returns the value of the first non-NULL operand, found by working
from left to right, or NULL if all the operands equal NULL.

/Jarl

#6

On Mar 2, 3:13*am, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:

Quote:

On 02.03.2009 03:02, Ed Prochak wrote:

On Mar 1, 8:29 pm, Fabrice <fabrice.gaut... (AT) gmail (DOT) com> wrote:
Replying to myself...

On Mar 1, 4:08 pm, Fabrice <fabrice.gaut... (AT) gmail (DOT) com> wrote:

[...]
I want for each Account, the sum of all amounts.
I used a simple query:
* * * * *SELECT
* * * * *Accounts.name ,
* * * * *sum(Logs.amount) AS balance
* * * * *FROM Accounts, Logs
* * * * *WHERE Accounts.id=Logs.account
* * * * *GROUP BY Accounts.id
But that does not do exactly do what I want because it does not
include the Accounts that are never referenced in the Log table. ( I
would like them to come out with either a 0 or NULL sum)
This query:

SELECT Accounts.id, Accounts.name, sum(Logs.amount) *FROM Accounts
LEFT OUTER JOIN Logs ON Accounts.id=Logs.account GROUP BY id;

is better in that it gives me a NULL sum.

Now if I could get a 0 instead of NULL that would be perfect.

I figured out how to use UNION to add a row with a 0 amount for each
account, but I could not find a way to do the aggregate function on
the results of the UNION...

In Oracle you would use the NVL() function.
SELECT Accounts.id, Accounts.name, sum(NVL(Logs.amount,0)) *FROM
Accounts
LEFT OUTER JOIN Logs ON Accounts.id=Logs.account GROUP BY id;

But since you didn't bother to mention what DBMS you use, that may be
the wrong answer.

For other RDBMS use CASE.

* * * * robert

Oracle supports CASE. I was just trying, indirectly, to emphasize that
the OP never mentioned what DBMS he uses. I guess the ESP reference
went right over Fabrice's head. A lot of the questioners fail to
mention which DBMS product they use. As much as SQL is standard, not
all products support the standard at the same level. CASE might not be
supported by his DBMS either.

Ed

#7

On 02.03.2009 21:42, Ed Prochak wrote:

Quote:

On Mar 2, 3:13 am, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:
On 02.03.2009 03:02, Ed Prochak wrote:

In Oracle you would use the NVL() function.
SELECT Accounts.id, Accounts.name, sum(NVL(Logs.amount,0)) FROM
Accounts
LEFT OUTER JOIN Logs ON Accounts.id=Logs.account GROUP BY id;
But since you didn't bother to mention what DBMS you use, that may be
the wrong answer.
For other RDBMS use CASE.

Oracle supports CASE.

I wasn't insinuating that you shouldn't use CASE with Oracle. In fact,
I frequently do. :-)

Quote:

I was just trying, indirectly, to emphasize that
the OP never mentioned what DBMS he uses.

Yes, that was obvious - at least to me. But then again, I'm trained by
c.d.o.s. :-)

Quote:

I guess the ESP reference
went right over Fabrice's head. A lot of the questioners fail to
mention which DBMS product they use. As much as SQL is standard, not
all products support the standard at the same level. CASE might not be
supported by his DBMS either.

Absolutely. It was just another option to try.

Cheers

robert

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

sum of accounts with no entries.

comp.databases comp.databases

sum of accounts with no entries. - 03-01-2009 , 06:08 PM

Re: sum of accounts with no entries. - 03-01-2009 , 07:29 PM

Re: sum of accounts with no entries. - 03-01-2009 , 08:02 PM

Re: sum of accounts with no entries. - 03-02-2009 , 02:13 AM

Re: sum of accounts with no entries. - 03-02-2009 , 03:46 AM

Re: sum of accounts with no entries. - 03-02-2009 , 02:42 PM

Re: sum of accounts with no entries. - 03-03-2009 , 01:32 AM