I'm facing a problem with the SUM() function with MS SQL Server and MySQL.
These are the values in the table:
docYear docMonth cli des agent code flag doctype qty
2017 3 C01 D1 A1 12345 X OR 3
2017 3 C01 D1 A1 12345 X FA 3
2017 11 C01 D1 A1 12345 X OR 3
2017 11 C01 D1 A1 12345 X FA 3
This SQL query:
SELECT
docYear
, docMonth
, cli
, agent
, code
, flag
, sum(
case
when
doctype = 'FA'
then
qty
else
0
end
) as qtySum
from
tableName
group by
docYear
, docMonth
, cli
, agent
, code
, flag
returns:
2017 3 C01 D1 A1 12345 X 3
2017 11 C01 D1 A1 12345 X 3
The same query, with the same data, in MySQL returns:
2017 3 C01 D1 A1 12345 X 6
2017 11 C01 D1 A1 12345 X 6
As you see, the SUM apparently do the work differently between SQL and MySQL.
There are differences for the SUM() function or aggregation rules between SQL and MySQL?
SQL version:
Microsoft SQL Server 2008 R2 (SP2)
MySQL version:
5.7.23
Edit: I can't update the versions. And the only MS SQL SERVER fiddle online I founded it's not working...
Thanks a lot!
I think "sum" or "case when" in MSSQL Server and MySQL are same, but GROUP BY in MySQL is different to MSSQL Server.
In standard SQL you should use aggregation functions around all of non-aggregated columns. but in your issue "doctype" column does not exists in aggregation functions or non-aggregated selected columns.
In fact the MySQL way of using GROUP BY differs from the SQL Server way.
In SQL Server when you remove "doctype in select query in fact you select on a list like this:
docYear docMonth cli des agent code flag qty
2017 3 C01 D1 A1 12345 X 3
2017 11 C01 D1 A1 12345 X 3
and your result in SQL Server is:
2017 3 C01 D1 A1 12345 X 3
2017 11 C01 D1 A1 12345 X 3
but MySQL logic in GROUP BY differs from the SQL Server and its difference is in dependency on non-aggregated selected columns in MySQL when you remove "doctype" from non-aggregated columns in fact you have a table like this:
docYear docMonth cli des agent code flag qty
2017 3 C01 D1 A1 12345 X 3
2017 3 C01 D1 A1 12345 X 3
2017 11 C01 D1 A1 12345 X 3
2017 11 C01 D1 A1 12345 X 3
In this link describe "MySQL Handling of GROUP BY"
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加