A brief example that retrieves a count of comments per project. We retrieve the counts for each each issue in a sub-select, and then aggregate by project and sum at the top level. Demonstrates the "count" and "sum" aggregate functions.

aggregates.sql

select
  ss.pname
  ,sum(ss.comment_count) as sum_comments
from (
select
  p.pname
  ,p.pkey || '-' || ji.issuenum AS issue_key
  ,count(distinct ja.id) as comment_count
from jiraissue ji
join project p
  on ji.project = p.id
join jiraaction ja
  on ji.id = ja.issueid
  and actiontype = 'comment'
group by 1,2
) ss
group by 1
order by sum_comments desc