mysql关联分组查询_用联合mysql选择查询分组

(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`)

上面的查询返回如下结果，

count name

1 Linda

2 Mary

1 Steve

1 Linda

此查询用于计算所有者拥有的传输数量。琳达有一辆汽车和一辆摩托车，所以结果应该是：

count name

2 Linda

2 Mary

1 Steve

我已经尝试过此查询，但返回错误：

(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

谁能帮我吗？