GROUP BY in SSMS vs MySql workbench

user_12810601 0
Viewed 83

Question : Write a query that obtains two columns. The first column must contain annual salaries higher than 80,000 dollars. The second column, renamed to “emps_with_same_salary”, must show the number of employees contracted to that salary. Lastly, sort the output by the first column. Need output in SSMS.

Sol:

Please note, this solution below gives the output in MySql Workbench but not in SSMS.

select salary, count(emp_no) as emps_with_same_salary
from salaries where salary > '80000' group by emp_no;

OUTPUT:

salary emps_with_same_salary

'80001' , '7'

'80007' , '11'

'80056' , '5'

aggregate-functions ssms group-by sql-server mysql
1 Answers

This is the query you should be using in SQL Server. It will likely work in MySQL as well but I do not know that particular SQL dialect.

select salary, count(emp_no) as emps_with_same_salary
from dbo.salaries 
where salary > 80000 
group by salary
order by salary;

Notice that I removed the single quotes around your constant in the WHERE clause. You don't (and shouldn't) enclose numeric constants in single quotes. This implies to the reader that your column is string when it usually is not. If your column is varchar (or similar), then you have a very different set of issues to address (since a salary of '9' > '80000'). Also notice the use of a schema-qualified table name - a best practice.

user_79261830
Related