-2

I have a table called - tbl_emp_info in MYSQL and MSSQL(SQL server) DB with same schema and data.

I am executing following query on this data -

MYSQL Query

 select count(*), name from tbl_emp_info group by dept;

MSSQL Query

 select count(*), name from tbl_emp_info group by dept;

When I execute the MYSQL query, it runs and gives me result. But when I execute the MSSQL query, it gives this error

SQL Error (8120): Column 'NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Now, I understand the error and I've checked it's ref - check this out : REF

Also, In MYSQL - It would give first value for NAME in the output and ignore the rest values for NAME when it does a "group by" by DEPT.

I need to fetch same results with MSSQL as well. Please enlighten.

Ankush Rathi
  • 622
  • 1
  • 6
  • 26
  • 1
    You are Grouping BY Dept, but selecting Name as a non aggregator column. There is the issue. – mkRabbani Jun 19 '19 at 08:24
  • MSSQL has strict rules on by default, it will not let you group, if the column not in the select area – Rohit.007 Jun 19 '19 at 08:25
  • Add some sample data and expected output result. This would help. – mkRabbani Jun 19 '19 at 08:26
  • Possible duplicate of [SQL Server - Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"](https://stackoverflow.com/questions/18258704/sql-server-column-invalid-in-the-select-list-because-it-is-not-contained-in-e) – Reyan Chougle Jun 19 '19 at 08:35
  • When you use aggregate functions, you cannot use columns that are not contained in aggregate function. This is for MSSQL. – dd_ Jun 19 '19 at 08:40
  • @everyone - guys, i get what the issue is. I just wanted to know is it possible to get results as MYSQL is allowing and giving an arbitrary name value for the group. – Ankush Rathi Jun 19 '19 at 09:54
  • @AnkushRathi . . . MySQL has been fixed. The default settings on the most recent versions would return a similar error. – Gordon Linoff Jun 19 '19 at 11:34
  • @GordonLinoff - thanks for the update. :) – Ankush Rathi Jun 20 '19 at 06:51
  • I'm voting to close this question as off-topic because - recent mysql verions have fixed this issue. – Ankush Rathi Jun 20 '19 at 06:52

8 Answers8

2

column name should be also in group by: select count(*), name from tbl_emp_info group by dept, name;

All not agregated and columns that you try to show have to be in group by.

dargin
  • 61
  • 4
2

MySQL has configurable behaviour when it comes to group by, so by default it will accept queries which are not conformant with the SQL-92 standard; MS SQL does not.

Your query makes no sense - "group by department" means you want to get the number of employees for each department. MySQL is just giving you an arbitrary name for each department, along with the count. I imagine that's not actually what you want.

If you want to group by department, your query should be:

select count(*), dept from tbl_emp_info group by dept;

If you want the department and the (alphabetically) first employee, it should be:

select count(*), min(name), dept from tbl_emp_info group by dept;
Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
1

You need to select the column while performing an aggregate in sql Your should be as below

select count(*), name,dept from tbl_emp_info group by dept,name;
Dale K
  • 25,246
  • 15
  • 42
  • 71
mip
  • 51
  • 3
1

try this: select name, count(1) from tbl_emp_info group by name; or select dept, count(1) from tbl_emp_info group by dept;

ekha
  • 11
  • 3
1

"Group by" clause collates the statistics based on the field used. In MSSQL, it is imperative to view the stats alongside each value in the grouping field. Here "select dept, count(*) from tbl_emp_info group by dept;" is the right query

Santhosh Udhay
  • 51
  • 2
1

I'm not exactly sure what you want from your query but I'm guessing you want a list of names in each department along with the number of those names in the department. if so then try this : using COUNT OVER Window function

select  NumOfNames = count(name)OVER(partition by dept)
       ,name 
from tbl_emp_info 
;
Mazhar
  • 3,797
  • 1
  • 12
  • 29
1

@Ankush Rathi You can only get the columns that you used in group by in select for MSSQL.

chaitanya
  • 352
  • 4
  • 14
0
select count(*), name from tbl_emp_info group by name;

the group by columns can only be used with aggregate function. In case you want to aggregate based on different column then you can use inline select over parition by instead

Mahesh Malpani
  • 1,782
  • 16
  • 27