-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 You should look in to subqueries. Basically, you can treat any query you make as if it were a table.
For example say I have the query SELECT id, summary, status FROM ticket I can use that as a subquery (specifically a sub-select) by doing soemthing like the following: SELECT status, count(*) FROM (SELECT id, summary, status FROM ticket) AS subquery GROUP BY status Which aggregates the number of tickets that fall in to each status. Again, this query could be simplified by avoiding the subselect, but I use it as an example. Generally, temporary tables and views are used for cases when you don't want to generate the query multiple times and the query will always be static. Best, CM Lubinski http://cmlubinski.info P.S. I use postgres. The syntax may differ slightly between databases, but the core concepts (unions, subqueries, count) should be consistent between them. klausfpga wrote: > Following command works from SQL, but it consists of two queries and > is not accepted by TRAC: > The first one creates a temporary VIew, > the second one groups the view > > CREATE TEMP VIEW opened_closed as > select > id, > date(t.time,'unixepoch') as dt, > 1 as new, > 0 as closed > FROM ticket as t > UNION ALL > SELECT > ticket as id, > date(c.time,'unixepoch') as dt, > 0 as new, > 1 as closed > FROM ticket_change as c > WHERE c.field = 'status' and c.newvalue = 'closed' > ORDER by dt; > > SELECT dt,sum(new),sum(closed) from opened_closed group by dt; > > > > So my current solution is to do following on the trac host from a > sqlite3 prompt: > I create a permanent view: > CREATE VIEW opened_closed as > select > id, > date(t.time,'unixepoch') as dt, > 1 as new, > 0 as closed > FROM ticket as t > UNION ALL > SELECT > ticket as id, > date(c.time,'unixepoch') as dt, > 0 as new, > 1 as closed > FROM ticket_change as c > WHERE c.field = 'status' and c.newvalue = 'closed' > ORDER by dt; > > then I can use below query from within trac: > > SELECT dt,sum(new),sum(closed) from opened_closed group by dt; > > > Not too elegant, but it seems to do the job. > > bye > > Klaus > > > On Mar 5, 9:15 am, klausfpga <[email protected]> wrote: >> HiCM, >> >> Thanks for your answer. >> >> Do you use mysql or sqlite? >> The query doesn't seem to work on sqlite. >> >> I get an error message: >> >>> SQL error: near "(": syntax error >> I can create following query to list all opened and closed tickets by >> date, however I'd like to have the SUM() of opened and closed tickets >> per day (just one line per day). >> >> SELECT >> id, >> date(t.time,'unixepoch') as dt, >> 1 as new, >> 0 as closed >> FROM ticket as t >> UNION ALL >> SELECT >> ticket as id, >> date(c.time,'unixepoch') as dt, >> 0 as new, >> 1 as closed >> FROM ticket_change as c >> WHERE c.field = 'status' and c.newvalue = 'closed' >> ORDER by dt; >> >> I could do some kind of cheating and create a VIEW in sqlite with >> above query. >> Then I would probably be able to group / sum the result of above >> query. >> This means however I could do this only at hosts where I have access >> to the sqlite data base (for creating the view) >> >> On Mar 5, 6:29 am, CM Lubinski <[email protected]> wrote: >> -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuRFesACgkQfzi1OiZiJLBKbQCeK8viThqgkGy3D9e9K9u7G6Dk OhkAn2R9f2kwUzJoEJqgKv+zifQ8pX7R =FWMw -----END PGP SIGNATURE----- -- You received this message because you are subscribed to the Google Groups "Trac Users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/trac-users?hl=en.
