One minor (but esential) correction:
count() has to be replaced by sum(), so:
select min(id) as id,dt,sum(new) as new,sum(fixed) as
fixed,sum(closed) as closed from
(
select
id,
date(t.time,'unixepoch') as dt,
1 as new,
0 as fixed,
0 as closed
FROM ticket as t
UNION ALL
select * from
(
SELECT
ticket as id,
date(c.time,'unixepoch') as dt,
0 as new,
0 as fixed,
1 as closed
FROM ticket_change as c
WHERE c.field = 'status' and c.newvalue = 'closed'
)
UNION ALL
select * from
(
SELECT
ticket as id,
date(c.time,'unixepoch') as dt,
0 as new,
1 as fixed,
0 as closed
FROM ticket_change as c
WHERE c.field = 'status' and c.newvalue = 'testing'
)
)
group by dt
order by dt desc
bye
Klaus
On Mar 6, 9:02 pm, klausfpga <[email protected]> wrote:
> 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.
