Search Postgresql Archives

Re: 9.3 view / cross join / flat table solution

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sat, 18 Oct 2014 12:13:00 -0700, Wells Oliver <wellsoliver@xxxxxxxxx>
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ć



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux