I have following data hierarchy: Report > has many Modules > has many Instances.... each instance might have a different category assigned to it (finite but unknown amount of categories)
I need to produce a report level aggregate of instance categories... something like this:
| report_id | category | # |
| 1 | 1 | 10|
| 1 | 2 | 5 |
| 2 | 7 | 2 |
| 2 | 4 | 14|
I am new to MySQL and I have tried the following:
mysql> SELECT report_id.report_id, module_id.module_id, instance.instance_id, violation.name
-> FROM report_id
-> LEFT JOIN module_id
-> ON report_id.report_id=module_id.report_id
-> LEFT JOIN instance
-> ON module_id.module_id = instance.module_id
-> LEFT JOIN violation
-> ON instance.violation_id = violation.violation_id
-> ORDER BY report_id.report_id
-> ;
To produce:
+-----------+-----------+-------------+----------------+
| report_id | module_id | instance_id | category |
+-----------+-----------+-------------+----------------+
| 1 | 1 | 1 | 1 |
| 1 | 2 | 5 | 1 |
| 1 | 1 | 2 | 2 |
| 1 | 1 | 3 | 3 |
| 1 | 1 | 4 | 3 |
| 1 | 3 | 6 | 3 |
| 2 | NULL | NULL | NULL |
| 3 | NULL | NULL | NULL |
+-----------+-----------+-------------+----------------+
I have also tried:
mysql> SELECT violation.name as Category, COUNT(instance.instance_id) AS NumberOfViolations FROM instance
-> LEFT JOIN violation
-> ON instance.violation_id=violation.violation_id
-> GROUP BY name;
+----------------+--------------------+
| Category | NumberOfViolations |
+----------------+--------------------+
| 1 | 2 |
| 2 | 1 |
| 3 | 3 |
+----------------+--------------------+
But this returns total category count for all the grandchildren. (now I need these totals separated by top level Report group)
I am having trouble finding the right syntax to marry these two queries to produce the table I show about. Any clues as to how to proceed would be helpful.
I do realized that I need someway to introduce SUM if (or count if) WHERE report's ID matched module's ID and module's ID matched instance ID but not sure how to incorporate this nested structure.