Geek Answers Handbook

How to add two SUM

Why doesn't the next job work?

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

It returns an empty string.

Next returns 1 for my dataset:

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

How can I add two amounts correctly?

All:

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 the stream in my model. This should explain the column names.

+5
source share
2 answers
SELECT COALESCE(SUM(startUserThreads.newForStartUser),0)+COALESCE(SUM(endUserThreads.newForEndUser),0) AS numNew ...
+6
source

From MySQL docs

SUM ([DISTINCT] expr)

Returns the amount of expr. If the return set has no rows, SUM () ZERO is returned. The DISTINCT keyword can be used in MySQL 5.0 to summarize only the various expr values.

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

() , COUNT(), MIN() SUM() ignore NULL. COUNT (*), .

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

+1

More articles:


All Articles