聚合查询中切换大小写

发布于 2024-11-01 15:53:33 字数 330 浏览 5 评论 0原文

我想在我的 SQL 查询中有一个 switch case，这样当分组依据没有对任何元素进行分组时，我不想聚合，否则我想聚合。这可能吗。

我的查询是这样的：

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

我收到此错误列“student_subjectid”在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。

提前致谢..

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

音栖息无 2024-11-08 15:53:33

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

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

回复收藏 0 原文

另类 2024-11-08 15:53:33

你到底为什么要把事情复杂化呢？

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

如果只有一个 Student_mark，它也是 SUM、AVG、MIN 和 MAX - 所以继续使用聚合即可！

编辑

最终满足您的要求的数据集通常没有意义。实现这一目标的方法是合并（联合）两个不同的结果

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

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

回复收藏 0 原文

~没有更多了~