Group by with union mysql select query

Question

(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?

Michał Powaga · Accepted Answer · 2013-03-18T17:51:08.790

170

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

edited Mar 18 '13 at 17:51

answered Dec 20 '11 at 09:48

Michał Powaga

22,561
8
51
62

1

Such a simple way to do it, yet I never would have thought of that. Awesome! – alanaktion Mar 15 '13 at 20:20
How do you add order and limit to? – David Addoteye Nov 28 '15 at 19:23
1

@DavidAddoteye you add order and limit to the very end. You can order on any of the outermost selected columns: `sum(qty)` or `name` in this example. – rmirabelle Aug 09 '16 at 16:51

score 9 · Answer 2 · edited May 17 '16 at 08:09

This may be what your after:

SELECT Count(Owner_ID), Name
FROM (
    SELECT M.Owner_ID, O.Name, T.Type
    FROM Transport As T, Owner As O, Motorbike As M
    WHERE T.Type = 'Motorbike'
    AND O.Owner_ID = M.Owner_ID
    AND T.Type_ID = M.Motorbike_ID

    UNION ALL

    SELECT C.Owner_ID, O.Name, T.Type
    FROM Transport As T, Owner As O, Car As C
    WHERE T.Type = 'Car'
    AND O.Owner_ID = C.Owner_ID
    AND T.Type_ID = C.Car_ID
)
GROUP BY Owner_ID

Authman Apatira · Answer 3 · 2011-12-20T09:26:20.713

1

Try this EDITED:

(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 GROUP BY motorbike.owner_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 car.owner_id)

edited Dec 20 '11 at 09:26

answered Dec 20 '11 at 08:53

Authman Apatira

3,994
1
26
33

What's the error that you are getting? If i have the error text, I can better debug the problem – Authman Apatira Dec 20 '11 at 09:16
Error Code : 1064 This is error i get. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY car.owner_id – user1103332 Dec 20 '11 at 09:22
2

Its run correctly. But the result still same. Linda appear twice as she have one car and one motorcycle. I want to make Linda appear once with count = 2 – user1103332 Dec 20 '11 at 09:40

Group by with union mysql select query

3 Answers3

Linked