I have the following query, selecting rows which are found inside that 2 subqueries:
First subquery is to get vehicles, second to get drivers.
That first select is to get values from both subqueries.
select
department as "Delegación",
company as Compañía,
count(distinct idClientTruck) as Vehículos,
round(sum(v_ko)/(v_docs*count(distinct idClientTruck))*100) as "% Docs. Vehículos",
count(distinct idDriver) as Conductores,
round(sum(d_ko)/(d_docs*count(distinct idDriver))*100) as "% Docs. Conductores"
from
(
(select
cd.name as department,
c.businessname as company,
(select count(*) from widoc_config where idwidocconfiggroup=cfg_g.idwidocconfiggroup and idstatus=71001 and required=true) as v_docs,
wv.idClientTruck as idClientTruck,
cfg.idwidocconfig as idWidocConfig,
cfg.iddocument as idDocument,
a.idstatus as idStatus,
if(a.idstatus=43002,"0","1") as v_ko
from
widoc_config_group cfg_g
join widoc_config cfg on cfg.idwidocconfiggroup=cfg_g.idwidocconfiggroup and cfg.idstatus=71001 and cfg.required=true
join widoc_client wc on wc.idclient=cfg_g.idclient and wc.idstatus=71001
join client c on c.idclient=wc.idclientassociated and c.idstatus_client=5004
join widoc_vehicle wv on wv.idwidocclient=wc.idwidocclient and wv.idstatus=71001
join client_truck t on t.idclient_truck=wv.idclienttruck and t.idstatus_truckpart=33005
join widoc_fulfill_vehicle fv on fv.idwidocconfiggroup=cfg_g.idwidocconfiggroup and fv.idwidocvehicle=wv.idwidocvehicle
left join attachment_group a on a.idclient=wc.idclientAssociated and a.iddocument=cfg.iddocument and a.idstatus!=43004 and a.idFK=wv.idClientTruck
left join client_department cd on cd.idClientDepartment=wc.idClientDepartment and cd.idStatus=71001
where
cfg_g.idclient = 3683
and cfg_g.identitytype=73004
and cfg_g.idstatus=71001
) x
UNION
(select
cd.name as department,
c.businessname as company,
(select count(*) from widoc_config where idwidocconfiggroup=cfg_g.idwidocconfiggroup and idstatus=71001 and required=true) as d_docs,
wd.idDriver as idDriver,
cfg.idwidocconfig as idWidocConfig,
cfg.iddocument as idDocument,
a.idstatus as idStatus,
if(a.idstatus=43002,"0","1") as d_ko
from
widoc_config_group cfg_g
join widoc_config cfg on cfg.idwidocconfiggroup=cfg_g.idwidocconfiggroup and cfg.idstatus=71001 and cfg.required=true
join widoc_client wc on wc.idclient=cfg_g.idclient and wc.idstatus=71001
join client c on c.idclient=wc.idclientassociated and c.idstatus_client=5004
join widoc_driver wd on wd.idwidocclient=wc.idwidocclient and wd.idstatus=71001
join driver d on d.iddriver=wd.iddriver and d.idstatus=71001
join widoc_fulfill_driver fd on fd.idwidocconfiggroup=cfg_g.idwidocconfiggroup and fd.idwidocdriver=wd.idwidocdriver
left join attachment_group a on a.idclient=wc.idclientAssociated and a.iddocument=cfg.iddocument and a.idstatus!=43004 and a.idFK=wd.idDriver
left join client_department cd on cd.idClientDepartment=wc.idClientDepartment and cd.idStatus=71001
where
cfg_g.idclient = 3683
and cfg_g.identitytype=73004
and cfg_g.idstatus=71001
) y
) group by department,company;
But I get this error:
Error Code: 1064. 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 'y ) group by department,company' at line 60
Can anyone help me finding this error? Thanks!
You don't need aliases for the 2 queries that participate in the UNION, but you do need one for the unioned query:
select
department as "Delegación",
company as Compañía,
count(distinct idClientTruck) as Vehículos,
round(sum(v_ko)/(v_docs*count(distinct idClientTruck))*100) as "% Docs. Vehículos",
count(distinct idDriver) as Conductores,
round(sum(d_ko)/(d_docs*count(distinct idDriver))*100) as "% Docs. Conductores"
from
(
select
cd.name as department,
c.businessname as company,
(select count(*) from widoc_config where idwidocconfiggroup=cfg_g.idwidocconfiggroup and idstatus=71001 and required=true) as v_docs,
wv.idClientTruck as idClientTruck,
null as idDriver,
cfg.idwidocconfig as idWidocConfig,
cfg.iddocument as idDocument,
a.idstatus as idStatus,
if(a.idstatus=43002,"0","1") as v_ko
from
widoc_config_group cfg_g
join widoc_config cfg on cfg.idwidocconfiggroup=cfg_g.idwidocconfiggroup and cfg.idstatus=71001 and cfg.required=true
join widoc_client wc on wc.idclient=cfg_g.idclient and wc.idstatus=71001
join client c on c.idclient=wc.idclientassociated and c.idstatus_client=5004
join widoc_vehicle wv on wv.idwidocclient=wc.idwidocclient and wv.idstatus=71001
join client_truck t on t.idclient_truck=wv.idclienttruck and t.idstatus_truckpart=33005
join widoc_fulfill_vehicle fv on fv.idwidocconfiggroup=cfg_g.idwidocconfiggroup and fv.idwidocvehicle=wv.idwidocvehicle
left join attachment_group a on a.idclient=wc.idclientAssociated and a.iddocument=cfg.iddocument and a.idstatus!=43004 and a.idFK=wv.idClientTruck
left join client_department cd on cd.idClientDepartment=wc.idClientDepartment and cd.idStatus=71001
where
cfg_g.idclient = 3683
and cfg_g.identitytype=73004
and cfg_g.idstatus=71001
UNION
select
cd.name as department,
c.businessname as company,
(select count(*) from widoc_config where idwidocconfiggroup=cfg_g.idwidocconfiggroup and idstatus=71001 and required=true) as d_docs,
null as idClientTruck,
wd.idDriver as idDriver,
cfg.idwidocconfig as idWidocConfig,
cfg.iddocument as idDocument,
a.idstatus as idStatus,
if(a.idstatus=43002,"0","1") as d_ko
from
widoc_config_group cfg_g
join widoc_config cfg on cfg.idwidocconfiggroup=cfg_g.idwidocconfiggroup and cfg.idstatus=71001 and cfg.required=true
join widoc_client wc on wc.idclient=cfg_g.idclient and wc.idstatus=71001
join client c on c.idclient=wc.idclientassociated and c.idstatus_client=5004
join widoc_driver wd on wd.idwidocclient=wc.idwidocclient and wd.idstatus=71001
join driver d on d.iddriver=wd.iddriver and d.idstatus=71001
join widoc_fulfill_driver fd on fd.idwidocconfiggroup=cfg_g.idwidocconfiggroup and fd.idwidocdriver=wd.idwidocdriver
left join attachment_group a on a.idclient=wc.idclientAssociated and a.iddocument=cfg.iddocument and a.idstatus!=43004 and a.idFK=wd.idDriver
left join client_department cd on cd.idClientDepartment=wc.idClientDepartment and cd.idStatus=71001
where
cfg_g.idclient = 3683
and cfg_g.identitytype=73004
and cfg_g.idstatus=71001
) t group by department,company;