Complex Group By Query (recursive?)

Question

Hi,

sorry for the title but I'm not sure how I should call it. I'm using PostgreSQL 8.3 and would be fine with non-ansi query proposals.

Suppose this schema:

TimeEntries
id              - int
entry_date      - date
tracked_seconds - int
project_id      - int

projects
id              - int
name            - string
path            - string

Projects are hirachical, I don't care about parent_id etc. instead I opted for the path column, example:

Renovate Home    - path = renovate-home
- Clean Kitchen  - path = renovate-home/clean-kitchen

I want a query that returns the following:

date - project_path - tracked_seconds(this project) - tracked_seconds (total of self and child projects)

Grouped by date and multiple times per project path so example data:

projects
name          path
Funny         funny
- Project     funny/project
-- Is Funny   funny/project/is-funny
Foo           foo
- Bar         foo/bar

If there are now TimeEntries for funny/project and foo/bar and i Issue this query:

SELECT         entry_date, p.path as project_path, sum(tracked_seconds) / 60
FROM           time_entries te
LEFT JOIN      projects p on te.project_id = p.id
GROUP BY       entry_date, p.path

I get this result:

entry_date        project_path           ?sum?
2010-10-01        funny/project          20
2010-10-01        foo/bar                10

What I want is this:

entry_date        project_path           direct_sum     total
2010-10-01        funny                  0              20
2010-10-01        funny/project          20             20 
2010-10-01        foo                    0              10
2010-10-01        foo/bar                10             10

Answer 1

This query ought to get you closer to your goal:

SELECT         entry_date, p.path as project_path, p2.path, sum(tracked_seconds) / 60
FROM           time_entries te
LEFT JOIN      projects p on te.project_id = p.id
LEFT JOIN      projects p2 on p2.path LIKE p.path + '/%'
GROUP BY       entry_date, p.path, p2.path