Sample report to sort tickets by the number of votes

Here's a basic SQL query that should return all the tickets that have been voted on sorted by the total number of votes. I'm not a SQL expert, so perhaps this can be improved or simplified.

SELECT  p.value AS __color__,
   id AS ticket, summary, component, version, milestone, t.type AS type, 
   owner, status,
   SUM (v.vote) AS votes,
   changetime AS _changetime,
   description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT OUTER JOIN votes v ON (v.resource = 'ticket/'||t.id)
  WHERE status <> 'closed' AND v.vote IS NOT NULL
  GROUP BY id 
  ORDER BY votes DESC, p.value, time

I'd vote for this ticket. BTW the SQL query above doesn't work. When I save it trac displays: ERROR: column "p.value" must appear in the GROUP BY clause or be used in an aggregate function

Replying to mauzer_tim:

I'd vote for this ticket. BTW the SQL query above doesn't work. When I save it trac displays: ERROR: column "p.value" must appear in the GROUP BY clause or be used in an aggregate function

I wrote the query for

Trac	0.11.2.1
Python	2.5.2
SQLite	3.3.4

It works for me, so maybe it's a configuration difference.

Here's the query I used with PostgreSQL:

SELECT p.value AS __color__,
   id AS ticket, summary, component, version, milestone, t.type AS type, 
   owner, status,
   votes,
   changetime AS _changetime,
   description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  join (select resource, sum(vote) as votes from votes group by resource) as v
     ON (v.resource = 'ticket/'||t.id)
  WHERE status <> 'closed'
  ORDER BY votes DESC, p.value, time

Reassigning ticket to new maintainer.

This report exists on t.e.o: t:report:34

(In [8473]) Added SQL query for a report that sorts tickets by the number of votes. SQL is taken from the t.e.o. site. Fixes #3655.

#4364 is a duplicate.