在SQL查询中获取聚合函数所需的错误 [英] Getting aggregate function needed error in sql query

问题描述

我有一张桌子

主题名称标记性别

数学10男性

数学20女

物理30位男性

物理40位女性

化学50男性

化学60女

我需要编写一个sql查询以显示上表，如下所示

主题名称男性女性

数学10 20

物理30 40

化学50 60.我该怎么做?

我尝试了以下查询

I have got a table

subjectname mark gender

maths 10 male

maths 20 female

physics 30 male

physics 40 female

chemistry 50 male

chemistry 60 female

i need to write an sql query to display the above table as follows

subjectname male female

maths 10 20

physics 30 40

chemistry 50 60 .How can i do this?

I tried the following query

select subjectname,case(gender) when 'male' then mark
else mark end  from Test1 group by(subjectname)

但出现错误

信息8120，第16级，状态1，第1行
选择列表中的"Test1.gender"列无效，因为它既不包含在聚合函数中也不在GROUP BY子句中.
信息8120，第16级，状态1，第1行
选择列表中的"Test1.mark"列无效，因为它既不包含在聚合函数中也不在GROUP BY子句中.
信息8120，第16级，状态1，第2行

but got error

Msg 8120, Level 16, State 1, Line 1
Column ''Test1.gender'' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column ''Test1.mark'' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column ''Test1.mark'' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

推荐答案

尝试此操作

选择StudentName，

最小值(当男性"时，性别标记为性别"，然后标记结尾)为男性，
最大值(当女"然后标记结尾时为性别")为女
来自学生
按StudentName分组

请检查表名和列名.

Try this

select StudentName ,

Min(case Gender when ''Male'' then Mark end)as Male,
Max( case Gender when ''Female''then Mark end )as Female
from Student
group by StudentName

Please check the table name and column name.

这篇关于在SQL查询中获取聚合函数所需的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！