sql - 如何从此查询中获取“temp”列值的总和？

Question

在此处输入图像描述如何获取临时列的总和：

select Split_Part("EmpName",'_',1),
       "EmployeeID",
       sum("TotalDays"::decimal) as "temp" 
from "Leave_Log" 
where date("StartDate") between '2018-01-01' and '2018-12-31' 
  and "Staus"='Approved' 
  and "EmployeeID" in (Select "UserName" 
                      from  "Master_Employees" 
                      where "Status"='Y')
group by "EmpName","EmployeeID" 
order by "EmpName"

标签： sqlpostgresqlpostgresql-9.2

Answer 1

看起来不对 GROUP BY：

select Split_Part("EmpName",'_',1) splited_name,
       "EmployeeID",
       sum("TotalDays"::decimal) as "temp" 
from "Leave_Log" 
where date("StartDate") between '2018-01-01' and '2018-12-31' 
  and "Staus"='Approved' 
  and "EmployeeID" in (Select "UserName" 
                      from  "Master_Employees" 
                      where "Status"='Y')
group by splited_name,"EmployeeID" 
order by splited_name

例如：如果你有桌子

CREATE TABLE public.users
(
  id integer NOT NULL DEFAULT nextval('users_user_id_seq'::regclass),
  email character varying(100) NOT NULL,
  salary integer,
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT users_email_key UNIQUE (email)
)

然后请求

SELECT Split_Part(email, '@', 2) split_email, sum(salary)
  FROM users
  GROUP BY email
  ORDER BY email

和

SELECT Split_Part(email, '@', 2) split_email, sum(salary)
  FROM users
  GROUP BY split_email
  ORDER BY split_email

会给你不一样的回应