sql - how to fix a double LEFT JOIN in a trac report -

sql - how to fix a double LEFT JOIN in a trac report -

we utilize customized version of trac people can give scores bugs based on 2 criteria, allow phone call them severity , importance.

for simplicity, allow database contains next 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 instance first row means user linus suggested 4 severity score of ticket #1.

now have next sql query in trac study utilize average scores:

select t.title, t.reporter, avg(vs.value), avg(vi.value), count(vs.value), count(vi.value) ticket t left bring together votes vs on vs.type = 'severity' , vs.ticket_id=t.id left bring together votes vi on vi.type = 'importance' , vi.ticket_id=t.id;

in hopes, should homecoming table next 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 ticket , votes are.

however, due way left join works, entries cartesian product of severity , importance votes, averages still valid, while both counts set 3x2=6 instead of right value.

is there simple way prepare query, returns want?

combine case statements aggregate functions:

select t.title, t.reporter, sum(case when v.type = 'importance' 1 else 0 end) count_imp, sum(case when v.type = 'severity' 1 else 0 end) count_sev, sum(case when v.type = 'importance' v.value else 0 end) / count_imp avg_imp, sum(case when v.type = 'severity' 1 else 0 end) / count_sev avg_sev ticket t left bring together votes v on v.ticket_id = t.id;

if trac sql doesn't allow re-use aliases count_imp , count_sev repeat statements in average calculation columns.

sql join trac