CODEWITHSUNDEEP
postgresqlgroup-by
user2742861
user2742861

Reputation: 340

postgreSQL group by or not

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.

Upvotes: 0

Views: 79

Answers (1)

Tomas Greif
Tomas Greif

Reputation: 22623

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

Upvotes: 1

Related Questions