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
– Andriy M, Commented Apr 18, 2011 at 5:06
u mean to say i should hv just left my question as it is and added the changes query as an edit.. — Giselle
– Giselle, Commented Apr 18, 2011 at 5: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
– Andriy M, Commented Apr 18, 2011 at 5: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
– Andriy M, Commented Apr 18, 2011 at 5:18

Andriy M · Accepted Answer · 2011-04-18 05:11:40Z

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, 2011 at 5:11

Andriy M

78k18 gold badges100 silver badges157 bronze badges

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

Comments

RichardTheKiwi · Accepted Answer · 2011-04-18 05:41:23Z

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, 2011 at 5:41

answered Apr 18, 2011 at 3:15

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

2 Comments

Giselle Over a year ago

that was just an example.. say I want to select some otehr propert of that student in that case like the subjectid then?

Giselle Over a year ago

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..

Collectives™ on Stack Overflow

Switch case in aggregate query

2 Answers 2

Comments

EDIT

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

EDIT

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related