CODEWITHSUNDEEP
mysqlsql
MoreScratch
MoreScratch

Reputation: 3083

UNION multiple SELECT statements on same table in MySQL

I am trying to generate a view via UNION with multiple SELECT statements. Here are the SELECT statements:

SELECT COUNT(*) AS `offline` FROM sensors WHERE mbits = 0 AND dirty = 0;
SELECT COUNT(*) AS `active` FROM sensors WHERE dirty != 1;
SELECT IFNULL(SUM(mbits), 0) AS `bitrate` FROM sensors;

What I need is:

offline | active | bitrate
--------------------------
0       |10      | 57009

I've tried to UNION the first 2 SELECTS:

SELECT COUNT(*) AS `offline` FROM sensors WHERE mbits = 0 AND dirty = 0
UNION
SELECT COUNT(*) AS `active` FROM sensors WHERE dirty != 1;

However, I get:

offline
-------
0 
10

I suspect that I might have to use a JOIN to do this. Any help would be appreciated.

Upvotes: 0

Views: 63

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

I recommend conditional aggregation, but taking advantage of MySQL shortcuts;

SELECT SUM(  mbits = 0 AND dirty = 0 ) AS`offline,
       SUM( dirty <> 1 ) as active,
       COALESCE(SUM(mbits), 0) AS bitrate
FROM sensor

Upvotes: 1

VN&#39;sCorner
VN&#39;sCorner

Reputation: 1552

Alternatively a not so efficient way of doing it,

SELECT offline,active,bitrate FROM
(SELECT COUNT(*) AS offline FROM sensors WHERE mbits = 0 AND dirty = 0) offline_tab,
(SELECT COUNT(*) AS active FROM sensors WHERE dirty != 1) active_tab,
(SELECT IFNULL(SUM(mbits), 0) AS bitrate FROM sensors) sum_bits tab;

Upvotes: 0

Olivier Depriester
Olivier Depriester

Reputation: 1625

You can do it with 1 query by using CASE WHEN. You simulate the COUNT(*) by summing 1 when your condition is met.

SELECT SUM(CASE WHEN mbits = 0 AND dirty = 1 THEN 1 ELSE 0 END CASE) AS `offline` ,
       SUM(CASE WHEN dirty <> 1 THEN 1 ELSE 0 END CASE) AS `active` ,
       IFNULL(SUM(mbits), 0) AS `bitrate`
FROM sensors;

Upvotes: 1

Related Questions