how to fix a double LEFT JOIN in a trac report

Question

We use a customized version of trac where people can give scores for the bugs based on two criteria, let us call them severity and importance.

For simplicity, let us say that the database contains the following tables:

Table ticket

id | title           | reporter
1  | SQL injection   | Torvalds
2  | Buffer Overflow | Linus
3  | Localization    | bofh

Table votes

ticket_id | value | type       | voter
1         | 4     | severity   | Linus
1         | 2     | severity   | Torvalds
1         | 3     | severity   | bofh
1         | 4     | importance | Linus
1         | 3     | importance | Torvalds
2         | 4     | severity   | Linus
2         | 2     | severity   | Torvalds
2         | 1     | importance | Linus
2         | 1     | importance | bofh
3         | 1     | importance | Linus

So for instance the first row means that the user Linus suggested 4 as the severity score of ticket #1.

Now I have the following SQL query in a trac report that I use to get the average scores:

SELECT t.title, t.reporter, AVG(vs.value), AVG(vi.value), COUNT(vs.value), COUNT(vi.value)
FROM ticket t
LEFT JOIN votes vs ON vs.type = 'severity'   AND vs.ticket_id=t.id
LEFT JOIN votes vi ON vi.type = 'importance' AND vi.ticket_id=t.id;

In my hopes, this should return a table with the following values:

title | reporter | severity avg | importance avg | number of sev. votes | number of imp. votes
SQL injection | Torvalds | 3 | 3.5 | 3 | 2

thus telling me how many people voted for the ticket and what their votes are.

However, due to the way LEFT JOIN works, the entries that I get are the cartesian product of severity and importance votes, so the averages are still valid, while both counts are set to 3x2=6 instead of the correct value.

Is there a simple way to fix this query, so that it returns what I want?

Answer 1

Combine CASE statements with aggregate functions:

SELECT 
  t.title, 
  t.reporter, 
  SUM(CASE WHEN v.type = 'importance' THEN 1 ELSE 0 END) AS Count_Imp,
  SUM(CASE WHEN v.type = 'severity' THEN 1 ELSE 0 END) AS Count_Sev,
  SUM(CASE WHEN v.type = 'importance' THEN v.value ELSE 0 END) / 
    Count_Imp AS Avg_Imp,
  SUM(CASE WHEN v.type = 'severity' THEN 1 ELSE 0 END) / 
    Count_Sev AS Avg_Sev
FROM 
  ticket t
  LEFT JOIN votes v 
  ON v.ticket_id = t.id;

If Trac SQL doesn't allow you to re-use the aliases Count_Imp and Count_Sev you would simply repeat those statements in the average calculation columns.