1

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
  • 3
    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
0

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

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.