CODEWITHSUNDEEP
mysqlsql
Jonah
Jonah

Reputation: 2080

How to add two SUMs

Why wont the following work?

SELECT SUM(startUserThreads.newForStartUser)+SUM(endUserThreads.newForEndUser) AS numNew ...

It returns an empty string.

The following is returning 1 for my data set however:

SELECT SUM(startUserThreads.newForStartUser) AS numNew ...

How do I add the two sums correctly?

The whole thing:

SELECT t.*,
       COUNT(startUserThreads.id) + COUNT(endUserThreads.id)                     AS numThreads,
       SUM(startUserThreads.newForStartUser) + SUM(endUserThreads.newForEndUser) AS numNew
FROM   `folder` `t`
       LEFT OUTER JOIN `thread` `startUserThreads`
         ON ( `startUserThreads`.`startUserFolder_id` = `t`.`id` )
       LEFT OUTER JOIN `thread` `endUserThreads`
         ON ( `endUserThreads`.`endUserFolder_id` = `t`.`id` )
WHERE  user_id = :user

FYI, only two users can share a thread in my model. That should explain my column names

Upvotes: 7

Views: 11273

Answers (2)

Saic Siquot
Saic Siquot

Reputation: 6513

SELECT COALESCE(SUM(startUserThreads.newForStartUser),0)+COALESCE(SUM(endUserThreads.newForEndUser),0) AS numNew ...

Upvotes: 10

dfb
dfb

Reputation: 13289

From the MySQL docs

SUM([DISTINCT] expr)

Returns the sum of expr. If the return set has no rows, SUM() returns NULL. The DISTINCT keyword can be used in MySQL 5.0 to sum only the distinct values of expr.

SUM() returns NULL if there were no matching rows.

Aggregate (summary) functions such as COUNT(), MIN(), and SUM() ignore NULL values. The exception to this is COUNT(*), which counts rows and not individual column values.

Maybe try COALESCE( SUM(x), 0 ) + COALESCE( SUM(y), 0 )?

Upvotes: 1

Related Questions