Mysql Error 1064 Subquery
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up mySQL Error 1064 in subquery up vote 2 down vote favorite 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 mysql subquery share|improve this question edited Dec 20 '15 at 15:44 enkrates 3931315 asked Dec 20 '15 at 15:11 HappyCane 716 Can you build a scheme with sqlfiddle.com? Maybe a simple blank is missing? –etalon11 Dec 20 '15 at 15:15 2 Group by clause issue, no aggregate function used in select list or add all columns in group clause –Muhammad Muazzam Dec 20 '15 at 15:18 No luck with fiddle either :( I'm learning sql on the go so I'm not very comfortable when it comes to su
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Syntax error in SQL subquery up vote 1 down vote favorite I http://stackoverflow.com/questions/34382409/mysql-error-1064-in-subquery am getting a syntax error can anybody tell me why? SELECT c.clientid, c.clientname, c.billingdate, (SELECT TOP 1 previousbalance FROM invoice i WHERE i.client = c.clientid ORDER BY i.invoiceid DESC) AS remaining FROM client c ORDER BY clientname What the secondary select is doing is getting the latest record for that clientid in the invoice table. The program - HediSQl SQL And here is the error: SQL Error (1064): You have http://stackoverflow.com/questions/12171354/syntax-error-in-sql-subquery an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 previousbalance FROM invoice i WHERE i.client = c.clientid ORDER BY i.invoicei' at line 1 */ mysql syntax share|improve this question edited Aug 29 '12 at 5:31 hims056 17.9k1664101 asked Aug 29 '12 at 5:22 user1269625 623155390 1 Please show the syntax error here. and which SQL (MySQL, SQL Server etc)? –hims056 Aug 29 '12 at 5:24 HediSQL is the program, SQL and here is the error SQL Error (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 '1 previousbalance FROM invoice i WHERE i.client = c.clientid ORDER BY i.invoicei' at line 1 */ –user1269625 Aug 29 '12 at 5:29 add a comment| 3 Answers 3 active oldest votes up vote 3 down vote accepted Just guessing but it might indicate that you should replace TOP 1 with LIMIT 1 or WHERE ROWNUM < 2 LIMIT 1. What kind of DB are you using? share|improve this answer edited Aug 29 '12 at 5:36 edze 2,28111023 answered Aug 29 '12 at 5:32 tbl 69
Go Groovy Haml Handlebars Haskell HTML HTTP Ini iOS Jade Java Javascript jQuery JSON Julia Keyman https://codedump.io/share/80WJCwMgpHAP/1/mysql-error-1064-in-subquery LaTeX Linux Less LOLCODE Makefile Markdown MATLAB MySQL NASM Node.js NSIS Objective-C Pascal Perl PHP PHP Extras PowerShell Python R React JSX reST (reStructuredText) Rip Ruby Rust SAS Sass (Sass) Sass (Scss) Scala Scheme Smalltalk Smarty SQL Stylus Swift Twig TypeScript Vb.net VHDL Wiki markup YAML Other Search HappyCane mysql error - 10 months ago 25x SQL Question mySQL Error 1064 in subquery 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
Anonymous Answer Email {} Share 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_I