user1103332 user1103332 - 6 months ago 11
SQL Question

Group by with union mysql select query

(SELECT COUNT(motorbike.`owner_id`) as count,owner.`name`,transport.`type` FROM transport,owner,motorbike WHERE transport.type='motobike'
AND owner.`owner_id`=motorbike.`owner_id`
AND transport.`type_id`=motorbike.`motorbike_id` GROUP BY motorbike.owner_id)
UNION ALL
(SELECT COUNT(car.`owner_id`) as count,owner.`name`,transport.`type` FROM transport,owner,car WHERE transport.type='car'
AND owner.`owner_id`=car.`owner_id`
AND transport.`type_id`=car.`car_id` GROUP BY car.`owner_id`)


The query above returns a result like this below,

count name
1 Linda
2 Mary
1 Steve
1 Linda


This query is to count how many transport that owned by an owner. Linda have one car and one motorcycle,so the result should:

count name
2 Linda
2 Mary
1 Steve


I have tried this query,but return error:

(SELECT COUNT(motorbike.`owner_id`),owner.`name`,transport.`type` FROM transport,owner,motorbike WHERE transport.type='motobike'
AND owner.`owner_id`=motorbike.`owner_id`
AND transport.`type_id`=motorbike.`motorbike_id`)
UNION ALL
(SELECT COUNT(car.`owner_id`),owner.`name`,transport.`type` FROM transport,owner,car WHERE transport.type='car'
AND owner.`owner_id`=car.`owner_id`
AND transport.`type_id`=car.`car_id`) GROUP BY motorbike.owner_id


Can anyone help me please?

Answer
select sum(qty), name
from (
    select count(m.owner_id) as qty, o.name
    from transport t,owner o,motorbike m
    where t.type='motobike' and o.owner_id=m.owner_id
        and t.type_id=m.motorbike_id
    group by m.owner_id

    union all

    select count(c.owner_id) as qty, o.name,
    from transport t,owner o,car c
    where t.type='car' and o.owner_id=c.owner_id and t.type_id=c.car_id
    group by c.owner_id
) t
group by name
Comments