Switch case in aggregate query

Question

I want to have a switch case in my SQL query such that when the group by does not group any element i dont want to aggregate otherwise I want to. Is that possible.

my query is something like this:

select count(1),AVG(student_mark) ,case when Count(1)=1 then student_subjectid else null end from Students
group by student_id

i get this error Column 'student_subjectid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thanks in advance..

The error message became irrelevant after editing the source. You should have appended the changed code with a sort of `EDIT` remark. Otherwise you should remove or replace the original error message, because it's confusing now. — Andriy M, Apr 18 '11 at 05:06
u mean to say i should hv just left my question as it is and added the changes query as an edit.. — Giselle, Apr 18 '11 at 05:12
I'm talking about this error message you've included in your question: `Column 'Student_mark' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.` I understand it is related to your query's previous version. Your updated script wouldn't produce this error. — Andriy M, Apr 18 '11 at 05:14
Well, usually the original poster adds the progress, if any, to the question post. I think, simply modifying the question (including any source code) is fine sometimes, but it is important to change all the relevant parts of the post, so overall it makes sense. — Andriy M, Apr 18 '11 at 05:18

score 2 · Accepted Answer · answered Apr 18 '11 at 05:11

2

SELECT
  student_id,
  COUNT(*) AS MarkCount,
  AVG(student_mark) AS student_mark,
  CASE COUNT(*) WHEN 1 THEN MIN(student_subjectid) END AS student_subjectid
FROM Students
GROUP BY student_id

answered Apr 18 '11 at 05:11

Andriy M

76,112
17
94
154

RichardTheKiwi · Answer 2 · 2011-04-18T05:41:23.760

1

Why in the world would you complicate it?

select count(1), AVG(Student_mark) Student_mark
from Students
group by student_id

If there is only one student_mark, it is also the SUM, AVG, MIN and MAX - so just continue to use the aggregate!

EDIT

The dataset that would eventuate with your requirement will not normally make sense. The way to achieve that would be to merge (union) two different results

select
    numRecords,
    Student_mark,
    case when numRecords = 1 then student_subjectid end    # else is implicitly NULL
from
(
select
    count(1) AS numRecords,
    AVG(Student_mark) Student_mark,
    min(student_subjectid) as student_subjectid
from Students
group by student_id
) x

edited Apr 18 '11 at 05:41

answered Apr 18 '11 at 03:15

RichardTheKiwi

105,798
26
196
262

that was just an example.. say I want to select some otehr propert of that student in that case like the subjectid then? – Giselle Apr 18 '11 at 03:51
although this soln is the simplest approach and I had this in mind.. for a small query like this it is fine.. but my actual query involves a lot of join and I cant have it run twice.. any otehr idea.. – Giselle Apr 18 '11 at 05:11

Switch case in aggregate query

2 Answers2

EDIT