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
SELECT colourone, COUNT(*) AS 'num' FROM vehicle_tbl GROUP BY colourone
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.
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');
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;
+-------+-------+ | color | count | +-------+-------+ | black | 2 | | blue | 1 | | red | 2 | | white | 3 | +-------+-------+