2

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
  • 4
    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 Sep 26 '18 at 18:01
  • 2
    Also use a proper JOIN. It's been around for over 20 years. – Eric Sep 26 '18 at 18:02
  • 2
    MySQL or MS SQL Server? those are two very different database products. – a_horse_with_no_name Sep 26 '18 at 18:26
1

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 Sep 27 '18 at 17:25
1

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.

  • 1
    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" – a_horse_with_no_name Mar 11 '20 at 12:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.