CODEWITHSUNDEEP
mysqlsql
Hamamelis
Hamamelis

Reputation: 2115

Mysql query union all

I've two queries executed in MySQL DB.

The only difference between the two queries is this condition additional:

WHERE
    Action > ''

The two queries:

mysql> SELECT
 COUNT(DISTINCT tel_number) AS `cases present`
FROM
    `tbl_tel_number`
WHERE
    Consuming IS NOT NULL
GROUP BY
    Zone,
    Consuming
LIMIT 3;

SELECT
 COUNT(DISTINCT tel_number) AS `cases analyzed`
FROM
    `tbl_tel_number`
WHERE
    Action > ''
GROUP BY
    Zone,
    Consuming
LIMIT 3;
+---------------+
| cases present |
+---------------+
|             7 |
|            27 |
|            27 |
+---------------+
3 rows in set

+-----------------+
| cases analyzed  |
+-----------------+
|               1 |
|               3 |
|               3 |
+-----------------+
3 rows in set

Now I trying union the result set of the two queries and tested this solution:

mysql> SELECT
 COUNT(DISTINCT tel_number) AS `cases present`,
 CASE
WHEN Action > '' THEN
    COUNT(DISTINCT tel_number)
ELSE
    0
END AS `cases analyzed`
FROM
    `tbl_tel_number`
WHERE
    Consuming IS NOT NULL
GROUP BY
    Zone,
    Consuming
LIMIT 3;


+---------------+-----------------+
| cases present | cases analyzed  |
+---------------+-----------------+
|             7 |               0 |
|            27 |               0 |
|            27 |              27 |
+---------------+-----------------+
3 rows in set

mysql>

But the output is wrong because in the second query for cases analyzed I've:

+-----------------+
| cases analyzed  |
+-----------------+
|               1 |
|               3 |
|               3 |
+-----------------+
3 rows in set

Instead in union queries for cases analyzed I've, why?

+-----------------+
| cases analyzed  |
+-----------------+
|               0 |
|               0 |
|              27 |
+-----------------+
3 rows in set

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You want to put the condition inside the count:

SELECT COUNT(DISTINCT tel_number) AS `cases present`,
       COUNT(DISTINCT CASE WHEN Action > '' THEN tel_number END) as `cases analyzed`
FROM `tbl_tel_number`
WHERE Consuming IS NOT NULL
GROUP BY Zone, Consuming
LIMIT 3;

Upvotes: 3

Related Questions