CODEWITHSUNDEEP
sqlt-sqlaggregate
Giselle
Giselle

Reputation: 23

Switch case in aggregate query

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

Upvotes: 2

Views: 488

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

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

Upvotes: 1

Andriy M
Andriy M

Reputation: 77687

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

Upvotes: 2

Related Questions