CODEWITHSUNDEEP
oracle-databasepentaho
ilanas
ilanas

Reputation: 117

Complete an oracle sql, add new colums with the sum of other column

One doubt, I am not an expert in sql oracle. I have the following sql:

select p.pname as Project, t.pname as Type, c.cname as component, w.timeworked/ 3600 as hours
from jira.jiraissue j,
jira.worklog w,
jira.project p,
jira.issuetype t,
jira.component c,
jira.nodeassociation na
where w.issueid = j.id
and J.PROJECT = P.ID
and na.source_node_id = j.id
AND na.sink_node_entity = 'Component'
AND na.source_node_entity = 'Issue'
and na.sink_node_id = c.id
and t.id = j.issuetype

and w.author = (case when $ {Author} = 'All' and then the author else $ {Author} ends)
and p.pname = (case when $ {Project} = 'All' then p.pname else $ {Project} final)
and t.pname = (case when $ {Type} = 'All' then t.pname else $ {type} final)
and c.cname = (case when $ {Component} = 'All' and then cname else $ {Component} final)
and to_char (w.startdate, 'yyyy-mm-dd')> = $ {FromDate}
and to_char (w.startdate, 'yyyy-mm-dd') <= $ {ToDate}

el resultado es la siguiente tabla: enter image description here

I would like to add a column with the sum of all rows HORAS and another with the sum grouped by COMPONENTE. Can someone give me advice if I can do what I want and how?

Upvotes: 1

Views: 233

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

Analytic functions might come in handy here:

SELECT
    p.pname AS Project,
    t.pname AS Type,
    c.cname AS component,
    w.timeworked / 3600 AS hours,
    SUM(w.timeworked / 3600) OVER () AS sum_all_hours,
    SUM(w.timeworked / 3600) OVER (PARTITION BY c.cname) AS sum_by_component
FROM jira.jiraissue j
... (the rest of your current query)

Upvotes: 2

Related Questions