Thread: 9.3 view / cross join / flat table solution

On Sat, 18 Oct 2014 12:13:00 -0700, Wells Oliver <wellsoliver@gmail.com>
wrote:
> Hi everyone. I have a view setup like so:
> 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ć