i want by group by 'Facturacion' and Motor Facturación Drools. I have this sql:
SELECT c.cname AS componente,
w.timeworked / 3600 AS horas,
Sum(w.timeworked / 3600)
OVER () AS sum_tipo,
Sum(w.timeworked / 3600)
OVER (
partition BY c.cname) AS sum_by_component
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 p.pname = 'Area Económica'
AND t.pname = 'Bug'
AND w.startdate BETWEEN To_date('01/10/2018', 'dd/mm/yyyy') AND
To_date('09/10/2018', 'dd/mm/yyyy')
the result is this:
if i put a select distinct, it grouped ok but some lines doesn't catch. the result is this:
i want quit the column HORAS and only watch 2 rows
use distinct
SELECT distinct c.cname AS componente,
Sum(w.timeworked / 3600) OVER () AS sum_tipo,
Sum(w.timeworked / 3600)
OVER (
partition BY c.cname) AS sum_by_component
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 p.pname = 'Area Económica'
AND t.pname = 'Bug'
AND w.startdate BETWEEN To_date('01/10/2018', 'dd/mm/yyyy') AND
To_date('09/10/2018', 'dd/mm/yyyy')