id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
25136	Add an example with Count('X', distinct=True) to the queries topic guide	Michał Domański	Caio Ariede	"The problem is the difference between the results of **count()** on **entries** relation and **num_entries** if I also want an annotation for **alerts** relation

{{{#!python
reports = Report.objects.filter(
        user=request.user).annotate(num_entries=Count('entries'), num_alerts=Count('alerts'))
print report.num_entries, report.entries.count()
}}}

Yet, this problem did not happen if there was only 1 argument to annotate.

What's happening? Well, both **entries** and **alerts** are FK relations, thus they result in this two left outer joins. The SQL is something like this:
{{{#!sql
SELECT `coverage_reports`.*
COUNT(`alerts_alert`.`id`) AS `num_alerts`,
COUNT(`coverage_reports_entry`.`id`) AS `num_entries`
FROM `coverage_reports_coveragereport`
LEFT OUTER JOIN `alerts_alert` ON ( `coverage_reports_coveragereport`.`id` = `alerts_alert`.`coverage_report_id` )
LEFT OUTER JOIN `coverage_reports_entry` ON (`coverage_reports_coveragereport`.`id` = `coverage_reports_entry`.`coverage_report_id` )
WHERE `coverage_reports_coveragereport`.`user_id` = 1 GROUP BY `coverage_reports_coveragereport`.`id` ;
}}}

This is problematic as joins will duplicate parent records if more than one child record is associated to it. This is what can inflate values from aggregate functions.

The fix to this particular query was such:
{{{#!sql
SELECT reports.id, alerts.num_alerts, entries.num_entries
FROM coverage_reports_coveragereport AS reports
LEFT JOIN
  (SELECT coverage_report_id, COUNT(*) AS num_alerts FROM alerts_alert GROUP BY coverage_report_id) AS alerts
ON reports.id = alerts.coverage_report_id
LEFT JOIN
  (SELECT coverage_report_id, COUNT(*) AS num_entries FROM coverage_reports_entry GROUP BY coverage_report_id) AS entries
ON reports.id = entries.coverage_report_id
WHERE reports.user_id = 1 GROUP BY reports.id;
}}}
"	Cleanup/optimization	closed	Documentation	1.8	Normal	fixed			Accepted	0	0	0	0	1	0