9.3 view / cross join / flat table solution

[Wells Oliver]

Hi everyone. I have a view setup like so:

create view myview as
select
department,
employee_id,
report_status,
sum(reports) reports
from logger.reports
group by department, employee_id, report_status;

Which gives me the total number of reports by status, which is either
'published' or 'finalized'.

What I don't get is an 'all' rollup. Which I could do with another flat
table and a cross join on a table of report statuses, but I'd love to keep
this is as a view if possible.

Using 9.3, so I have the "latest and greatest". What are my options here?

[m***@kset.org]

screate view myview asselect department, employee_id,
report_status, sum(reports) reportsfrom logger.reportsgroup by
department, employee_id, report_statu;
Which gives me the total number of reports by status, which is either
'published' or 'finalized'.
What I don't get is an 'all' rollup. Which I could do with another flat
table and a cross join on a table of report statuses, but I'd love to
keep this is as a view if possible.
Using 9.3, so I have the "latest and greatest". What are my options here?

It is a bit unclear what you expect to get.
If you need to get all published and finalized reports summed together,
and reported with the published and finalized, only one select will not
suffice. Maybe something like this:

create view myview as
(select department, employee_id, report_status, sum(reports)
reports
from logger.reports
group by department, employee_id, report_status
union all
select department, employee_id, 'all' as report_status,
sum(reports) reports
from logger.reports
group by department, employee_id )

Additional sorting may be required if you need the rows for published,
finalized and all to be near each other in the results.

Regards,
Mladen Marinović