Neil Fifteen Neil Fifteen - 3 months ago 9
MySQL Question

MYSQL Counting and grouping across two columns if value exists in either column

This is my first question, so I apologise if it is not in the correct format. I have found similar answers but they require specific inputs and I'm not overly experienced in MySQL to know how to take them out.

I have a table like this:

type | colourone | colourtwo
-----+-----------+----------
car | red | white
car | red | blue
van | white | NULL
car | black | NULL
can | white | black


I'm trying to count all the colours and group them in one SELECT, but across both columns. It doesn't matter which column a colour appears in, as long as it appears and is counted.
The outcome would look something like this:

red, 2
white, 3
blue, 1
black, 2


The same colour will not appear in both columns in a row and some rows have only one colour, hence the NULL in the second. I did find something that was on the right line, but required me to use an "IN()" to pick out values. I only want to count them.

I can complete one column but don't know how to involve the second column, without running a second SELECT then adding them together. I have the first one like this:

SELECT colourone, COUNT(*) AS 'num' FROM vehicle_tbl GROUP BY colourone

Answer

Here is my or with a derived table d. The derived table d was constructed with a union to get non-dupe colors (as opposed to UNION ALL that returns dupes). We just needed a color list. It would include NULL, but as NULL will not join back in the join, we didn't need to fear that.

The counts are at the thing - level and therefore used the id. So if for a given thing it was both red and red, it would only count once (at the thing level).

That edge condition was not presented in the data. I doubt it would fail.

Note that the column names or aliases of the first union clause are the ones the whole union uses. That would explain the lazy approach in the second union part.

Schema:

create table thing
(   id int auto_increment primary key,
    type varchar(100) not null,
    colourone varchar(100) null,
    colourtwo varchar(100) null
);
insert thing (type,colourone,colourtwo) values
('car','red','white'),
('car','red','blue'),
('van','white',NULL),
('car','black',NULL),
('can','white','black');

query:

select d.color,count(t.id) as 'count' 
from 
(   select colourone as 'color' from thing 
    union 
    select colourtwo from thing 
) d 
join thing t 
on t.colourone=d.color or t.colourtwo=d.color 
group by d.color 
order by d.color;

results:

+-------+-------+
| color | count |
+-------+-------+
| black |     2 |
| blue  |     1 |
| red   |     2 |
| white |     3 |
+-------+-------+
Comments