Geek asks and answers

MySQL UNION - combining returned values ​​in a single row row

I am new to MySQL and ask you to ask for help. I select 2 sets of count values ​​from one table in 2 parts of the join, using different WHERE values ​​for differentiation; eg:

SELECT Count(column1) as 'RED', null as 'BLUE'
FROM TableA
Where column1 Like 'RED'
UNION
SELECT NULL, Count(column1)
FROM TableA
Where column1 Like 'BLUE'

I was hoping to get:

RED      BLUE
---      ----
23       55

But I really get:

RED      BLUE
---      ----
23       NULL
NULL     55

Can someone tell me what I did, and how can I get the desired answer? Thank you for your help...

+3
source share
4 answers

This is not suitable for UNION. You can accomplish what you are trying to do using SUMand CASE. Here is an example using your query:

SELECT SUM(CASE WHEN column1 LIKE 'RED' THEN 1 ELSE 0 END) AS red_count,
SUM(CASE WHEN column1 LIKE 'BLUE' THEN 1 ELSE 0 END) AS blue_count
FROM TableA
WHERE column1 LIKE 'RED'
OR column1 LIKE 'BLUE'
+3
source

Here is a request for you

select
    A.colorcount RED,
    B.colorcount BLUE
from
    (select count(1) colorcount from tableA where column1='RED') A,
    (select count(1) colorcount from tableA where column1='BLUE') B
;

Here is sample data

mysql> drop database if exists encue;
Query OK, 1 row affected (0.05 sec)

mysql> create database encue;
Query OK, 1 row affected (0.01 sec)

mysql> use encue
Database changed
mysql> create table tableA
    -> (
    ->     id int not null auto_increment,
    ->     column1 varchar(20),
    ->     primary key (id),
    ->     key (column1)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> insert into tableA (column1) values
    -> ('RED'),('BLUE'),('BLUE'),('BLUE'),('RED'),('BLUE'),
    -> ('RED'),('BLUE'),('BLUE'),('BLUE'),('RED'),('BLUE'),
    -> ('RED'),('BLUE'),('BLUE'),('BLUE'),('RED'),('BLUE'),
    -> ('RED'),('BLUE'),('BLUE'),('BLUE'),('RED'),('BLUE'),
    -> ('RED'),('BLUE'),('BLUE'),('BLUE'),('RED'),('BLUE'),
    -> ('RED'),('BLUE'),('BLUE'),('BLUE'),('RED'),('BLUE'),
    -> ('RED'),('BLUE'),('BLUE'),('BLUE'),('RED'),('BLUE'),
    -> ('RED'),('BLUE'),('BLUE'),('BLUE'),('RED'),('BLUE'),
    -> ('RED'),('BLUE'),('BLUE'),('BLUE'),('RED'),('BLUE'),
    -> ('RED'),('BLUE'),('BLUE'),('BLUE'),('RED'),('BLUE'),
    -> ('RED'),('BLUE'),('BLUE'),('BLUE'),('RED'),('BLUE'),
    -> ('BLUE'),('BLUE'),('BLUE'),('BLUE'),('BLUE'),('BLUE'),
    -> ('BLUE'),('BLUE'),('BLUE'),('BLUE'),('BLUE'),('RED');
select count(1) colorcount,column1 from tableA group by column1;
Query OK, 78 rows affected (0.10 sec)
Records: 78  Duplicates: 0  Warnings: 0

mysql> select count(1) colorcount,column1 from tableA group by column1;
+------------+---------+
| colorcount | column1 |
+------------+---------+
|         55 | BLUE    |
|         23 | RED     |
+------------+---------+
2 rows in set (0.00 sec)

Here is the result you are looking for:

mysql> select A.colorcount RED,B.colorcount BLUE from
    -> (select count(1) colorcount from tableA where column1='RED') A,
    -> (select count(1) colorcount from tableA where column1='BLUE') B
    -> ;
+-----+------+
| RED | BLUE |
+-----+------+
|  23 |   55 |
+-----+------+
1 row in set (0.00 sec)

mysql>

Give it a try !!!

+1

:

SELECT Count(column1) as red, null as blue
FROM TableA Where column1 Like 'RED'
UNION
SELECT NULL as red, Count(column1) as blue
FROM TableA Where column1 Like 'BLUE'

?

0

SQL

SELECT Count(A.column1) as red, Count(B.column1) as blue FROM TableA as A,  TableA as B Where A.column1 Like 'RED' AND B.column1 Like 'BLUE'
0

More articles:


All Articles