MySQL query with LIKE operator for multiple values, with aggregate [closed]

Question

How should I write a query using the LIKE operator checking for multiple values.

Something similar to LIKE ('car%','bus%')

output I want:

car            373
Bus            2
Heavy Vehicle  3
Motor Cycle    79

As you can see from the desired output above, I need to subtotal each car, Bus, Heavy Vehicle, etc.

Answer 1

Switch to REGEXP:

WHERE vehicletype REGEXP '^(car|bus)'

Answer 2

You can define a derived table with your patterns, example:

select v.* 
from vehicle v 
join ( select 'Car%' as s union all select 'Bus%' ) as p 
    on v.vehicletype like p.s;

alternatively use a temp table:

create temporary table patterns (p varchar(20) not null);
insert into patterns (p) values ('Car%'),('Bus%');
select v.* 
from vehicle v 
join patterns as p 
    on v.vehicletype like p.p;

Edit: aggregate volume, group by pattern

select substr(p.p,1,length(p.p)-1), sum(volume) 
from vehicle v 
join patterns as p 
    on v.vehicletype like p.p 
group by substr(p.p,1,length(p.p)-1);

Answer 3

You can simply use the OR in your MySQL query. For example

SELECT * FROM `tableName` WHERE vehicletype LIKE '%val1%' OR vehicletype LIKE '%val2%'