UNION with multiple tables in MYSQL issue

Question

I tried it but no luck,I have two tables "crm_rentals" and "crm_sales".

Both have same structure.

Id | portals_name
1  | {dubizzle}{JustRentals}{JustProperty}{propertyfinder}{bayut}
2  | {dubizzle}{JustRentals}{JustProperty}{propertyfinder}{bayut}
3  | {JustRentals}{JustProperty}{propertyfinder}
4  | {dubizzle}{JustProperty}{bayut}

I want to get NUMBER of each portal in both tables,here is what i tried

select sum(dubizzle) dubizzle,sum(JustRentals) JustRentals,
sum(JustProperty) JustProperty,sum(propertyfinder) propertyfinder 
from ( (select count(id) as dubizzle from crm_rentals where 
portals_name like '%dubizzle%'
UNION
select count(id) as dubizzle from crm_sales where portals_name 
like  '%dubizzle%'
) a ,
(select count(id) as JustRentals from crm_rentals where 
 portals_name like '%JustRentals%'
UNION
select count(id) as JustRentals from crm_sales where 
portals_name like  '%JustRentals%') b,
(select count(id)  as JustProperty from crm_rentals where 
portals_name like '%JustProperty%'
UNION
select count(id)  as JustProperty from crm_sales where portals_name 
like '%JustProperty%') c ,
(select count(id) as propertyfinder from crm_rentals where 
portals_name like '%propertyfinder%'
UNION
select count(id) as propertyfinder from crm_rentals where 
portals_name like '%propertyfinder%'
) d )

I want to get result like

Dubizzle    JustRentals  JustProperty Propertyfinder Others
100           100         100          100            100

Question: I can not get this result,my query give me Syntax error.

UPDATE I tried this but syntax error

select * from (select @table1:=(select count(id) as dubizzle 
from  crm_rentals where portals_name like '%dubizzle%') a,
@table2:=(select count(id) as dubizzle from crm_sales 
where portals_name   like '%dubizzle%') b, (@table1 +@table2) 
as dubizzle) f,
((select @table1:=(select count(id) as JustRentals from 
crm_rentals where portals_name like '%JustRentals%') c,
@table2:=(select count(id) as JustRentals from crm_sales 
where portals_name like '%JustRentals%') d, (@table1 +@table2)
 as JustRentals)  ff) AS f

My question is, I can not get this result,my query give me issue. — Muhammad Ali, Oct 18 '15 at 08:40
I have not got time now to write a complete answer, but it might be worth your while to look at this post for ideas: [dynamic pivot table with MySql](http://stackoverflow.com/questions/12630128/mysql-dynamic-pivot). In your case the pivot query mabe does not need to be dynamic, so just use the generated select query shown in the post directly. You should also be able to get away with just doing one `UNION ALL` with your tables and then work from there. — Carsten Massmann, Oct 18 '15 at 08:52
My car give me an issue. Can you tell me wahts wrong? Is it so hard to tell what issue you have? Do you think we are mindreaders? — Giorgi Nakeuri, Oct 18 '15 at 08:52
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 ') a , — Muhammad Ali, Oct 18 '15 at 10:41

score 1 · Answer 1 · answered Oct 18 '15 at 09:22

you can try by this way

SELECT * FROM ( 
(select count(id) as dubizzle from crm_rentals where portals_name like '%dubizzle%') AS a,
(select count(id) as JustRentals from crm_rentals where portals_name like '%JustRentals%') b,
(select count(id)  as JustProperty from crm_rentals where portals_name like '%JustProperty%') AS c
UNION
(select count(id) as dubizzle from crm_sales where portals_name like '%dubizzle%') AS a,
(select count(id) as JustRentals from crm_sales where portals_name like  '%JustRentals%') AS b,
(select count(id)  as JustProperty from crm_sales where portals_name like '%JustProperty%') AS c
)

Liniel · Answer 2 · 2015-10-18T11:41:12.313

    select
    sum(a.dubizzle) as dubizzle,
    sum(b.JustRentals) as JustRentals,
    sum(c.JustProperty) as JustProperty,
    sum(d.propertyfinder) as propertyfinder 
from
(
    (
    select count(id) as dubizzle
    from crm_rentals
    where portals_name like '%dubizzle%'
    UNION
    select count(id) as dubizzle
    from crm_sales
    where portals_name like '%dubizzle%'
    ) as a ,

    (
    select count(id) as JustRentals
    from crm_rentals
    where portals_name like '%JustRentals%'
    UNION
    select count(id) as JustRentals
    from crm_sales
    where portals_name like  '%JustRentals%'
    ) as b,

    (
    select count(id)  as JustProperty
    from crm_rentals
    where portals_name like '%JustProperty%'
    UNION
    select count(id)  as JustProperty
    from crm_sales
    where portals_name like '%JustProperty%'
    ) as c ,

    (
    select count(id) as propertyfinder
    from crm_rentals
    where portals_name like '%propertyfinder%'
    UNION
    select count(id) as propertyfinder
    from crm_rentals
    where portals_name like '%propertyfinder%'
    ) as d
)

Please try this and return here query result.

BTW try to keep your code clear :)

server version for the right syntax to use near ') a , at line 16 — Muhammad Ali, Oct 18 '15 at 11:31
Try edit sub queries like this: ( select count(id) as dubizzle from crm_rentals where portals_name like '%dubizzle%' UNION select count(id) as dubizzle from crm_sales where portals_name like '%dubizzle%' ) as a , ) I mean add "as" statement to alias — Liniel, Oct 18 '15 at 11:38

UNION with multiple tables in MYSQL issue

2 Answers2