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
– alroc, Commented Feb 3, 2015 at 14:29

Community · Accepted Answer · 2017-05-23 11:43:32Z

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, 2017 at 11:43

CommunityBot

11 silver badge

answered Feb 3, 2015 at 14:50

Nicholai

8187 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alroc · Accepted Answer · 2015-02-03 14:51:06Z

0

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.

answered Feb 3, 2015 at 14:51

alroc

28.3k6 gold badges55 silver badges106 bronze badges

Collectives™ on Stack Overflow

MSSQL Query should return same as MYSQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related