0

There is a problem I have experienced in mssql. ( but I don't have this problem in mysql. ) They know two different engines. but I could not find a solution. Do you think I can fix this error?


My query is

SELECT
    COUNT(*) as stok_adet, s.*, m.*, d.*
    FROM stok as s
    CROSS JOIN mermer_cins as m
    JOIN stok_durum as d ON s.stok_durum_id = d.stok_durum_id
    WHERE m.mermer_cins_id='5' AND s.blok_no='M6320'
    GROUP BY s.bundle_no, s.boy, s.yukseklik, s.hatali
    ORDER BY s.blok_no ASC, s.bundle_no ASC, s.stok_tarih DESC" 


And the error reason I get in mssql

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



These are the design of my tables in mssql.

mermer_cins table=> https://pasteboard.co/IofwCGc.jpg
stok table => https://pasteboard.co/Iofx1QV.jpg
stok_durum table => https://pasteboard.co/IofxfJg.jpg

Here is the screenshot link of my running query in mysql. => https://pasteboard.co/Iof1DVa.png

  • 5
    MySQL prefers to return random results over correct SQL syntax. – a_horse_with_no_name Jul 16 at 14:39
  • So how do I deal with this problem? How should I make changes in sql? – Kasim ŞEN Jul 16 at 14:51
  • 3
    MySQL allow non std GROUP BY sentences by disabling ONLY_FULL_GROUP_BY flag. SQL Server requires the proper GROUP BY syntax. – McNets Jul 16 at 14:53
  • 1
    You deal with this problem by building better requirements and not just adding * from all tables to the output. If you're taking a count based on unique combinations of s.bundle_no, s.boy, s.yukseklik, s.hatali, let's say you have two rows where those four columns are the same, but s_blok_no and stok_id are not the same. Which s_blok_no and stok_id do you want to see in that case? Do you want a row for each unique combination or do you want some arbitrary value or ...? Just because MySQL is sloppy and allows non-standard behavior doesn't mean you need non-standard behavior. – Aaron Bertrand Jul 16 at 15:51
0

As soon as you use either an aggregate function (SUM(), COUNT(), etc.) or try to GROUP BY some column, every column within the SELECT list will have to be included in the GROUP BY. MS Documentation here.

To return results you'll either have to group by every column in the tables, or filter out the columns that you don't need from the SELECT (and add whatever column remains to the GROUP BY).

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.