Menu
(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?
256
There's no need to run GROUP BY on the contents when you're using UNION - UNION ensures that duplicates are removed; UNION ALL is faster because it doesn't - and in that case you would need the GROUP BY... There are use cases for wanting to use GROUP BY on the results of a union.
The UNION operator is used to combine the data from the result of two or more SELECT command queries into a single distinct result set. This operator removes any duplicates present in the results being combined.
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. The original idea was to create the table in beginning of the query, so the (SELECT * FROM #TBL) could be used on the query itself, instead of defining the names on each GROUP BY.
Both GROUP BY and ORDER BY are clauses (or statements) that serve similar functions; that is to sort query results. However, each of these serve very different purposes; so different in fact, that they can be employed separately or together.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
