postgreSQL组或不是 [英] postgreSQL group by or not

问题描述

以下代码重现：

  name | time_ | 
 ----------------------------------- 
 stackoverflow 3:00 
 google 5:35 
 stackoverflow 1:00 
 
 SELECT agenc.name，（SELECT CAST（servic.end_hour as time） -  CAST（servic.begin_hour as time））AS time_，jobs .name，
 FROM服务AS服务
 JOIN services_jobs AS job on jobs.id = servic.job_id 
 JOIN代理机构AS agenc ON agenc.id = jobs.agency_id 
 WHERE 
 EXTRACT（MONTH FROM servic.service_date）= 9 AND 
 EXTRACT（日期从servic.service_date）= 16 
  

我希望它以这种方式出现：

  name | time_ | 
 ----------------------------------- 
 stackoverflow 4:00 
 google 5:35 
  

可能吗？如果是这样，怎么样？

我曾经想过分组，但是我没有得到任何结果。

解决方案

很难在这里帮助，我想你正在寻找 sum 和 group by ：

  select 
 agenc.name，
 sum（cast（servic.end_hour as time） -  cast （servic.begin_hour as time））as time_ 
 from 
 services as servic 
 join services_jobs AS jobs on jobs.id = servic.job_id 
加入agency as agenc ON agenc.id = jobs.agency_id 
其中
提取（月份来自servic.service_date）= 9和
提取（日期自servic.service_date）= 16 
组合
 agenc。名称
  

I'm trying to group by the name the results.

The following code reproduces:

    name           |    time_     |   
-----------------------------------
    stackoverflow       3:00
    google              5:35
    stackoverflow       1:00

SELECT agenc.name, (SELECT CAST(servic.end_hour as time) - CAST(servic.begin_hour as time)) AS time_, jobs.name,
FROM services AS servic  
JOIN services_jobs AS jobs ON jobs.id = servic.job_id  
JOIN agency AS agenc ON agenc.id = jobs.agency_id
WHERE  
EXTRACT(MONTH FROM servic.service_date) = 9 AND  
EXTRACT(DAY FROM servic.service_date) = 16

And I want it to appears this way:

    name           |    time_     |   
-----------------------------------
    stackoverflow       4:00
    google              5:35

Is it possible? If so, how?

I thought about grouping, but I didn't get any result.

解决方案

Difficult to help here, I guess you are looking for sum and group by:

select 
  agenc.name, 
  sum(cast(servic.end_hour as time) - cast(servic.begin_hour as time)) as time_
from 
  services as servic  
  join services_jobs AS jobs ON jobs.id = servic.job_id  
  join agency as agenc ON agenc.id = jobs.agency_id
where  
  extract(month from servic.service_date) = 9 and  
  extract(day from servic.service_date) = 16
group by
  agenc.name

这篇关于postgreSQL组或不是的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！