MSSQL Query should return same as MYSQL

Question

I've MYSQL Query and working fine The QUERY IS:

select tst_type, count(tst_type) tot from tst_type where project='JupiQA';

The above Query returns single record If I'm adding GROUP BY tst_type in this query it returns multiple values.

The Query I tried in MSSQL without GROUP BY tst_type ,its showing Error

ERROR IS: Column 'tst_type.tst_type' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

After that added GROUP BY tst_type in MSSQL query,then working fine and returns multiple value.

But my requirement is it should return same as MYSQL without adding GROUP BY fn OR should return single value like MYSQL

What does that single record represent in your first query? The total number of that particular `tst_type` records, or a count of all records? — alroc, Feb 03 '15 at 14:29

score 0 · Answer 1 · edited May 23 '17 at 11:43

0

MySQL allows this behavior, but I don't know of any other DBMS that lets it happen. If you want MySQL and MSSQL queries to behave the same, add the SQL Mode ONLY_FULL_GROUP_BY. MySQL will now throw the error properly.

Also check out this SO post: Why does MySQL allow "group by" queries WITHOUT aggregate functions?

edited May 23 '17 at 11:43

Community

1
1

answered Feb 03 '15 at 14:50

Nicholai

818
7
17

score 0 · Answer 2 · answered Feb 03 '15 at 14:51

When doing a COUNT() as you're doing here, the GROUP BY is required to get the correct results. See the MySQL docs on the function

You can use a count(*) without any other fields to get a count of the total number of records. Otherwise, you must use GROUP BY.

IMHO your requirement for this query is backwards; aggregate functions require GROUP BY to behave properly/consistently when non-aggregate fields are included in the select. MSSQL is behaving properly, MySQL is not.

MSSQL Query should return same as MYSQL

2 Answers2