mySQL Error 1064 in subquery

HappyCane Published at Dev

HappyCane

I know it may be silly but I've spent 2 hours looking for the error and my deadline is running short. Can you help?

Here's the code

create view Children as
select avg(chAvg) as Avg_Children
from
(select CHILDREN.ID,
CHILDREN.PAT_ID,
CHILDREN.GENDER,
DRUGS.DRUG_TYPE,
count(CHILDREN.ID)/count(distinct(CHILDREN.PAT_ID)) as 'chAvg'
from CHILDREN
join PATIENTS
on CHILDREN.PAT_ID = PATIENTS.PATIENT_ID
join CASES
on PATIENTS.PATIENT_ID=CASES.PAT_ID
join DRUGS_TO_CASES
on CASES.CASE_ID = DRUGS_TO_CASES.CASE_ID
join DRUGS
on DRUGS_TO_CASES.DRUG_ID=DRUGS.ID
where DRUGS.DRUG_TYPE = 'tranquillisers'
group by CHILDREN.GENDER)

And here's what Workbench says:

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 '' at line 19

Mihai

Use an alias on the subquery and try to avoid the same name as the table even if mysql allows it

create view VChildren as
select avg(chAvg) as Avg_Children
from
(select CHILDREN.ID,
CHILDREN.PAT_ID,
CHILDREN.GENDER,
DRUGS.DRUG_TYPE,
count(CHILDREN.ID)/count(distinct(CHILDREN.PAT_ID)) as 'chAvg'
from CHILDREN
join PATIENTS
on CHILDREN.PAT_ID = PATIENTS.PATIENT_ID
join CASES
on PATIENTS.PATIENT_ID=CASES.PAT_ID
join DRUGS_TO_CASES
on CASES.CASE_ID = DRUGS_TO_CASES.CASE_ID
join DRUGS
on DRUGS_TO_CASES.DRUG_ID=DRUGS.ID
where DRUGS.DRUG_TYPE = 'tranquillisers'
group by CHILDREN.GENDER)x

Collected from the Internet

Please contact [email protected] to delete if infringement.