CODEWITHSUNDEEP
sqlsql-servergroup-by
OmniOwl
OmniOwl

Reputation: 5709

Group By doesn't seem to work as I expected (SQL Server)

I have the following query:

SELECT 
    AVG(CAST(RATING AS NUMERIC(18, 2))) AS AVERAGE,
    QUESTIONID,
    COUNT(QUESTIONID) AS COUNT
FROM
    AnswersRating
WHERE 
    SURVEYID IN (SELECT ID
                 FROM SURVEYS
                 WHERE FLIGHTDATAID = 7277)
GROUP BY 
    QUESTIONID;

And I get this result:

AVERAGE  QUESTIONID COUNT
-------------------------
3.606060 115        99
4.303030 109        99
2.969696 118        99
3.818181 112        99
2.545454 113        99
3.787878 121        99
3.606060 110        99
2.363636 119        99
3.515151 116        99
3.272727 117        99
4.242424 111        99
3.909090 120        99
2.333333 114        99

Which is nice and all except the QUESTIONID row is not ordered by the ID from lowest to highest which is what I want. I made the exact same query on another flight where things appear correctly:

AVERAGE  QUESTIONID COUNT
--------------------------
3.000000 109        1
3.000000 110        1
3.000000 111        1
3.000000 112        1
0.000000 113        1
0.000000 114        1
3.000000 115        1
0.000000 116        1
3.000000 117        1
3.000000 118        1
0.000000 119        1
3.000000 120        1
3.000000 121        1

What am I doing wrong?

Upvotes: 0

Views: 61

Answers (2)

Tedo G.
Tedo G.

Reputation: 1565

Why don't you use Order By? Grouping does not mean ordering.

ORDER BY QUESTIONID;

Upvotes: 4

Bacon Bits
Bacon Bits

Reputation: 32145

If you want a specific order for your results, you must specify an ORDER BY, which comes after the GROUP BY in clause order. The two clauses are not mutually exclusive:

SELECT AVG(CAST(RATING AS NUMERIC(18, 2))) AS AVERAGE,
     QUESTIONID,
     COUNT(QUESTIONID) AS COUNT
FROM AnswersRating
WHERE SURVEYID IN
(
   SELECT ID
   FROM SURVEYS
   WHERE FLIGHTDATAID = 7277
)
GROUP BY QUESTIONID
ORDER BY QUESTIONID;

Your second query is returning the results in order purely by coincidence.

Upvotes: 4

Related Questions