Using GROUP BY and ORDER BY in my query works in MySQL but not in SQL Server

Question

I have a query which runs correctly on MySQL server. In SQL Server it gives error:

"Column 'Tovar.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" .

I don't know how to convert it in to SQL Server. Looking forward for the help from guru's.

SELECT B.BBarcode,
       Name, C.Country,
       Manufacturer,W.id,
       Packtype,
       B.TovarId,
       SUM(WCount) as count,
       Price,
       Series,
       ExpDate
FROM Warehouse W, Tovar T, Barcodes B, Countries C 
WHERE C.CId=T.Country
   AND B.WarehouseId=W.id
   AND WCount>0
   AND T.id=W.TovarId +'=@param' 
GROUP BY W.ID,B.BBarcode 
ORDER BY Name

All those non-aggregated columns must be in GROUP BY clause. Your query will only run in MySQL. Your query won't even run in any other dbms because it just doesn't make sense. — Eric
– Eric, Commented Sep 26, 2018 at 18:01
Also use a proper JOIN. It's been around for over 20 years. — Eric
– Eric, Commented Sep 26, 2018 at 18:02
MySQL or MS SQL Server? those are two very different database products. — user1822
– user1822, Commented Sep 26, 2018 at 18:26

Aaron · Accepted Answer · 2018-09-26 18:19:38Z

To make this run all of the columns in the select have to be in the group by portion of the query.

Select B.BBarcode , Name, C.Country, Manufacturer,W.id,
 Packtype, B.TovarId, sum(WCount) as count, Price, Series, ExpDate
  from Warehouse W, Tovar T, Barcodes B, 
  Countries C 
  where C.CId=T.Country and
   B.WarehouseId=W.id and WCount>0 and 
   T.id=W.TovarId +'=@param' 
   GROUP BY B.BBarcode , Name, C.Country, Manufacturer,W.id,
 Packtype, B.TovarId, Price, Series, ExpDate
   ORDER BY Name

However I’m not sure the query will run with the where syntax you have, specifically the T.id=W.TovarId +'=@param' portion.

That can be refactored to be either

T.id=W.TovarId and T.id = @param

Or

T.id=W.TovarId and W.TovarId= @param

Version that should execute

Select B.BBarcode , Name, C.Country, Manufacturer,W.id,
 Packtype, B.TovarId, sum(WCount) as count, Price, Series, ExpDate
  from Warehouse W, Tovar T, Barcodes B, 
  Countries C 
  where C.CId=T.Country and
   B.WarehouseId=W.id and WCount>0 and 
   T.id=W.TovarId and T.Id = @param
   GROUP BY B.BBarcode , Name, C.Country, Manufacturer,W.id,
 Packtype, B.TovarId, Price, Series, ExpDate
   ORDER BY Name

I would also look at some documents on how sql server does joins and the group by. While I believe sql server will do the joins correctly for an inner join between the tables based on how you’ve written them, the syntax structure they have make it a little more clear what is joining to what as well as the options to do other types of joins

Thanks aaron , your query worked . But instead of adding all the fields in the Group by clause Iam trying with Join by clause . — Rajesh Stephen
– Rajesh Stephen, Commented Sep 27, 2018 at 17:25

Phill W. · Accepted Answer · 2020-03-11 11:32:55Z

MySQL lets you "get away" with "sloppy" queries like this.

Other DBMSs do not. SQL Server is one of them.

When using an aggregating query, you must do one or the other with every field in the select list - "group by" it, or aggregate it.

Here's why:

select * from table1 ; 

+---+----+---+ 
| A | B  | C | 
+---+----+---+ 
| 1 | 10 | Z | 
| 1 | 20 | Y | 
| 1 | 30 | X | 
| 2 | 40 | W | 
+---+----+---+ 

select A, sum( B ) SB, C 
from table1
group by A 
order by A ; 

+---+----+---+ 
| A | SB | C | 
+---+----+---+ 
| 1 | 60 | ? | <-- What values goes here? 'X', 'Y' or 'Z'? 
| 2 | 40 | W | 
+---+----+---+

Unless you tell the DBMS how to deal with these "stray" values, it cannot reliably determine the result should be, so it gives up and throws the error.

The reason that you "get away" with it in MySQL is that it takes the attitude of ...

If you don't care which value you want, nor do I!

... and gives you any one of the values, with no rhyme or reason as to which.

I think MySQL's attitude is better described as "Prevent error message at all costs, even if that means returning random results or ignoring data consistency" — user1822
– user1822, Commented Mar 11, 2020 at 12:14

Stack Exchange Network

Using GROUP BY and ORDER BY in my query works in MySQL but not in SQL Server

2 Answers 2

Your Answer

Hot Network Questions

Using GROUP BY and ORDER BY in my query works in MySQL but not in SQL Server

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions