Hi All,
Brand new to both Jira and SQL so please bear with me!
I'm using SQL to select the time worked on certain activities by employees. The base query works fine (validated via other means). However, when I add a simple 'group by' to the query, only a fraction of the data is returned compared to the original.
Base query used:
select
wl.author,
truncate(wl.timeworked/3600, 2) as 'Time (Hrs)'
from
jiraissue ji
join customfieldvalue cfv on cfv.ISSUE = ji.ID
join customfield cf on cf.ID = cfv.CUSTOMFIELD
join customfieldoption cfo on cfo.CUSTOMFIELD = cf.ID and cfo.ID = cfv.STRINGVALUE join worklog wl on wl.issueid = ji.ID
join project p on p.ID = ji.PROJECT
where
cf.cfname = 'Bill work to Customer' and
cfo.customvalue <> 'Internal' and
(wl.startdate >= '2023-07-01 07:00:00') and (wl.startdate < '2023-10-01 07:00:00')
This returns 4630 rows, equating to 93 unique users and a total time of 6436.78 hrs.
Modified query (just adding group by):
select
wl.author,
truncate(wl.timeworked/3600, 2) as 'Time (Hrs)'
from
jiraissue ji
join customfieldvalue cfv on cfv.ISSUE = ji.ID
join customfield cf on cf.ID = cfv.CUSTOMFIELD
join customfieldoption cfo on cfo.CUSTOMFIELD = cf.ID and cfo.ID = cfv.STRINGVALUE join worklog wl on wl.issueid = ji.ID
join project p on p.ID = ji.PROJECT
where
cf.cfname = 'Bill work to Customer' and
cfo.customvalue <> 'Internal' and
(wl.startdate >= '2023-07-01 07:00:00') and (wl.startdate < '2023-10-01 07:00:00')
group by wl.author
This returns 93 rows (as expected) but only a total of 430.04 hrs.
Is anyone able to explain to me why the addition of a 'group by' would cause this effect?
Thanks in advance for helping a noob...!
