Hi CM,
Thanks.
Yes indeed sqlite supports subqueries.
The problem seems just to be a copy paste error in the example, that
you posted
you had one more '(' than you had ')'
My query without viewsm but with subqueries looks now:
select id,dt,count(new) as new,count(closed) as closed from
(
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'
)
group by dt;
;
On Mar 5, 3:32 pm, CM Lubinski <[email protected]> wrote:
> -----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 Lubinskihttp://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.
