Vipar Vipar - 4 months ago 10
SQL Question

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?

Answer

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.

Comments