Fri Nov 25 2016
Copied to clipboard! Copy reply
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
SELECT Name, SUM(TotalIssues) AS 'Total Issues', SUM(ClosedIssues) AS 'Closed ISSUES'
FROM
(
	SELECT Names.name AS 'Name', Counts.TotalIssues AS 'TotalIssues', IFNULL(Counts.ClosedIssues, 0) AS 'ClosedIssues' FROM
	(
		SELECT A.url_id AS 'CountID', A.COUNT AS 'TotalIssues', B.COUNT AS 'ClosedIssues' 
		FROM 
			( select url_id, count(id) as 'COUNT' FROM issues GROUP BY url_id)A 
			LEFT JOIN
			(select url_id, count(id) AS 'COUNT' FROM issues WHERE status = 'ignored' OR status='fixed' GROUP BY url_id)B
			ON A.url_id = B.url_id
	)Counts
	INNER JOIN
	(
		SELECT urls.id AS url_id, sets.name
		FROM urls INNER JOIN sets ON urls.set_id = sets.id 
	)Names
	ON Counts.url_id = Names.url_id
) CountsBySet
GROUP BY Name