# Thread: Combining queries and getting the sum of a value from each

1. ## Returning 0 instead of NULL in COUNT function

I have a query with two COUNT functions in it. In my SELECT clause I want to get the sum of these two values. Something like this...
SELECT SUM(count1 + count2) AS total

The problem is that if no records are counted in a COUNT function, it returns NULL, thus making the SUM in the SELECT clause NULL even if one of the COUNT values is not NULL.

So my question is: How can I have the COUNT functions return 0 instead of NULL when there are no records to be counted?

2. Code:
`SELECT SUM(COALESCE(COUNT(col1), 0) + COALESCE(COUNT(col2), 0))`
http://dev.mysql.com/doc/refman/5.0/...ction_coalesce

3. I think you need to use the DISTINCT keyword. COUNT(DISTINCT whatever) FROM table though Dan's solution will also work

4. The COALESCE function was just what I needed. Thanks.

5. ## Combining queries and getting the sum of a value from each

I have two queries, each containing a COUNT value. I want to combine the two queries into one and get the total sum of the two COUNT values.

Here is what I have, but I get errors:

Code MySQL:
``` SELECT (
COALESCE( alias1.id_count, 0 ) + COALESCE( alias2.id_count, 0 )
) AS total
FROM (

SELECT COUNT( table1.id ) AS id_count
FROM table1
.........
) AS alias1
UNION (

SELECT COUNT( table2.id ) AS id_count
FROM table2
.........
) AS alias2```

6. Code:
```SELECT alias1.id_count + alias2.id_count AS total
FROM
(SELECT COUNT( table1.id ) AS id_count
FROM table1
.........
) AS alias1
INNER JOIN
(SELECT COUNT( table2.id ) AS id_count
FROM table2
.........
) AS alias2```

7. Perfect. Thanks.

8. guido, if you're going to drop the ON condition in an INNER JOIN, you really should code it as a CROSS JOIN instead

here's another approach --
Code:
```SELECT SUM(id_count) AS total
FROM (
SELECT COUNT(*) AS id_count
FROM table1
.........
UNION ALL
SELECT COUNT(*)
FROM table2
.........
) AS u```
note no COALESCE required here either

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•