Error in MySQL code- Syntax error at Inner join

Question

I have 2 Tables - Department and Employees. I am trying to display department that hire at least 1 employee which must be sorted by department id

Department Table

create table department (
      dept_id integer not null,
      dept_name varchar(30) not null,
      dept_location varchar(30) not null,
      unique(dept_id)
  );

Employee Table


  create table employee (
      emp_id integer not null,
      emp_name varchar(50) not null,
      dept_id integer not null,
      salary integer not null,
      unique(emp_id)
  );

I have written this SQL query

SELECT  department.dept_id, sum(salary),count(*) 
FROM employee 
     INNER JOIN department
       ON employee.dept_id =department.dept_id
        GROUP BY department.dept_id
          HAVING COUNT(*) > 0;
ORDER BY department.dept_id

I am getting

syntax error at or near "ORDER"

Delete ; before order by

forpas
– forpas

2019-06-14 19:02:43 +00:00
Commented Jun 14, 2019 at 19:02 — forpas
– forpas, Commented Jun 14, 2019 at 19:02

ScaisEdge · Accepted Answer · 2019-06-14 18:56:30Z

1

You have the order by clause in wrong position ..

the order must be the last clause

SELECT  department.dept_id, sum(employee.salary), count(*) 
FROM employee 
     INNER JOIN department
       ON employee.dept_id =department.dept_id
        GROUP BY department.dept_id
          HAVING COUNT(*) > 0
ORDER BY department.dept_id;

this because the ORDER BY just work for result presentation

edited Jun 14, 2019 at 18:56

answered Jun 14, 2019 at 18:46

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

ScaisEdge Over a year ago

.. try execute the code .. the error message in based on wrong sequence of the clause .. and during the parsing the inner join is in an wron postion respected thye previsous code .. but the reason is the wrong position of the ORDER BY clause ..

forpas Over a year ago

Use the table's name: ORDER BY department.dept_id

ScaisEdge Over a year ago

if you have ambiguity .. then just add the table name to the order by ... answer updated ..

ScaisEdge Over a year ago

@forpas thanks for the suggestion .. upvote somewhere

ScaisEdge Over a year ago

@Karan .. update your question and show me you exect error message

|

Collectives™ on Stack Overflow

Error in MySQL code- Syntax error at Inner join

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related