必须出现在GROUP BY子句中或用于聚合函数（PostgreSQL）(Must appear in the GROUP BY clause or be used in an aggregate fu

必须出现在GROUP BY子句中或用于聚合函数（PostgreSQL）(Must appear in the GROUP BY clause or be used in an aggregate function (PostgreSQL))

我有这样的查询：

select u.fullname ,s.schedate, sum(case when s.isvisiting =1 then s.isvisiting else 0 end) as visit , count(s.custid) as cust, sum(case when s.isclosing = 1 then s.isclosing else 0 end)as orders from vmstrschedule s JOIN vmsmsuser u ON u.usercode = s.salesmanid where u.branchid = 'BLL' group by u.fullname

但它显示如下错误：

必须出现在GROUP BY子句中或用于聚合函数

它适用于MySQL，但是当我在PostgreSQL中尝试它时它没有用。 我想每月显示这样的数据：

但如果我使用这个查询：

select u.fullname,s.schedate, sum(case when s.isvisiting =1 then s.isvisiting else 0 end) as visit , count(s.custid) as cust, sum(case when s.isclosing = 1 then s.isclosing else 0 end)as orders from vmstrschedule s JOIN vmsmsuser u ON u.usercode = s.salesmanid where u.branchid = 'BLL' group by u.fullname,s.schedate order by u.fullname

它将显示如下数据：

I have query like this :

select u.fullname ,s.schedate, sum(case when s.isvisiting =1 then s.isvisiting else 0 end) as visit , count(s.custid) as cust, sum(case when s.isclosing = 1 then s.isclosing else 0 end)as orders from vmstrschedule s JOIN vmsmsuser u ON u.usercode = s.salesmanid where u.branchid = 'BLL' group by u.fullname

but it shows error like this :

must appear in the GROUP BY clause or be used in an aggregate function

it works in MySQL , but when I tried it in PostgreSQL it didn't work . I want to display data like this , per month :

but if I used this query :

select u.fullname,s.schedate, sum(case when s.isvisiting =1 then s.isvisiting else 0 end) as visit , count(s.custid) as cust, sum(case when s.isclosing = 1 then s.isclosing else 0 end)as orders from vmstrschedule s JOIN vmsmsuser u ON u.usercode = s.salesmanid where u.branchid = 'BLL' group by u.fullname,s.schedate order by u.fullname

it will shows data like this :

最满意答案

如果通过“工作”你的意思是它任意选择每个组的一个可能的schedate值，你是正确的。 但是，postgresql和大多数其他SQL产品都希望您明确指出应该选择什么值

如果每个组中的所有值都相同，则将它放在GROUP BY子句或任意聚合（例如MAX(s.schedate) ）中都应该有效。 如果值不同，请告诉服务器应该选择哪一个 - MIN或MAX通常是合适的。

If by "works" you mean it arbitrarily selects one of the possible schedate values for each group, you're correct. However, postgresql and most other SQL products want you to be explicit about what value they should select

If all the values are the same within each group, putting it in either the GROUP BY clause or an arbitrary aggregate (e.g. MAX(s.schedate)) should work. If the values are different, please tell the server which one it should select - the MIN, or MAX would usually be appropriate.