PostgreSQL - Unable to use PARTITION BY when multiple columns are involved

Question

Suppose I have a table with 5 columns:

• YEAR
• COMPANY
• DEPARTMENT
• EMPLOYEE
• SALES

And I wanted to query the results to get the following results:

• COMPANY
• DEPARTMENT
• EMPLOYEE
• SUM(SALES)

The additional requirement being that the COMPANY with the Maximum Sales (across all departments) shows first, and then the DEPARTMENT within the company with the Maximum Sales shows first for the company, and the same for EMPLOYEE column.

SUCCESS: The following query works fine when I query ONLY for the COMPANY:

 SELECT company,
       SUM(sales)
FROM   company_sales
GROUP  BY company
ORDER  BY SUM(SUM(sales))
            over (
              PARTITION BY company) DESC,
          company;

SUCCESS: The following query works fine when I group by COMPANY & DEPARTMENT:

SELECT company,
       department,
       SUM(sales)
FROM   company_sales
GROUP  BY company,
          department
ORDER  BY SUM(SUM(sales))
            over (
              PARTITION BY company) DESC,
          SUM(SUM(sales))
            over (
              PARTITION BY department) DESC,
          company,
          department;

FAILURE: The following query FAILS when I group by COMPANY, DEPARTMENT & EMPLOYEE:

SELECT company,
       department,
       employee,
       SUM(sales)
FROM   company_sales
GROUP  BY company,
          department,
          employee
ORDER  BY SUM(SUM(sales))
            over (
              PARTITION BY company) DESC,
          SUM(SUM(sales))
            over (
              PARTITION BY department) DESC,
          SUM(SUM(sales))
            over (
              PARTITION BY employee) DESC,
          company,
          department,
          employee;

Here's the above query with Data for your Use:

WITH COMPANY_SALES AS (
SELECT  2010    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-1' as employee,  100 as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-2' as employee,  101 as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-3' as employee,  102 as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-1' as employee,  50  as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-2' as employee,  51  as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-3' as employee,  52  as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-4' as employee,  53  as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-5' as employee,  54  as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-1' as employee,  80  as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-2' as employee,  81  as sales
UNION SELECT    2010    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-3' as employee,  82  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-1' as employee,  20  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-2' as employee,  21  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-3' as employee,  22  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-1' as employee,  90  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-2' as employee,  91  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-3' as employee,  92  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-4' as employee,  93  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-5' as employee,  94  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-6' as employee,  95  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-C' as department, 'Employee-B-C-1' as employee,  40  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-1' as employee,  60  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-2' as employee,  61  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-3' as employee,  62  as sales
UNION SELECT    2010    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-4' as employee,  63  as sales
UNION SELECT    2010    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-1' as employee,  20  as sales
UNION SELECT    2010    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-2' as employee,  21  as sales
UNION SELECT    2010    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-3' as employee,  22  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-1' as employee,  30  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-2' as employee,  29  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-3' as employee,  28  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-4' as employee,  27  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-1' as employee,  45  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-2' as employee,  25  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-3' as employee,  78  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-4' as employee,  23  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-1' as employee,  12  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-2' as employee,  54  as sales
UNION SELECT    2011    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-3' as employee,  79  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-1' as employee,  34  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-2' as employee,  89  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-3' as employee,  60  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-1' as employee,  23  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-2' as employee,  62  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-3' as employee,  45  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-4' as employee,  78  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-5' as employee,  45  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-6' as employee,  9   as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-C' as department, 'Employee-B-C-1' as employee,  73  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-1' as employee,  93  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-2' as employee,  27  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-3' as employee,  59  as sales
UNION SELECT    2011    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-4' as employee,  73  as sales
UNION SELECT    2011    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-1' as employee,  54  as sales
UNION SELECT    2011    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-2' as employee,  67  as sales
UNION SELECT    2011    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-3' as employee,  84  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-1' as employee,  78  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-2' as employee,  69  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-3' as employee,  45  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-A' as department, 'Employee-A-A-4' as employee,  69  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-1' as employee,  24  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-2' as employee,  35  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-3' as employee,  89  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-4' as employee,  64  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-B' as department, 'Employee-A-B-5' as employee,  75  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-1' as employee,  15  as sales
UNION SELECT    2012    as year, 'Company-A' as company, 'Department-A-C' as department, 'Employee-A-C-2' as employee,  38  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-1' as employee,  46  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-2' as employee,  85  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-A' as department, 'Employee-B-A-3' as employee,  63  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-1' as employee,  24  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-2' as employee,  59  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-3' as employee,  76  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-4' as employee,  18  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-5' as employee,  94  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-B' as department, 'Employee-B-B-6' as employee,  55  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-C' as department, 'Employee-B-C-1' as employee,  69  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-1' as employee,  37  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-2' as employee,  84  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-3' as employee,  52  as sales
UNION SELECT    2012    as year, 'Company-B' as company, 'Department-B-D' as department, 'Employee-B-D-4' as employee,  55  as sales
UNION SELECT    2012    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-1' as employee,  78  as sales
UNION SELECT    2012    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-2' as employee,  94  as sales
UNION SELECT    2012    as year, 'Company-C' as company, 'Department-C-A' as department, 'Employee-C-A-3' as employee,  21  as sales
)
SELECT company,
       department,
       employee,
       SUM(sales)
FROM company_sales
GROUP  BY company,
          department,
          employee
ORDER  BY SUM(SUM(sales))
            over (
              PARTITION BY company) DESC,
          SUM(SUM(sales))
            over (
              PARTITION BY department) DESC,
          SUM(SUM(sales))
            over (
              PARTITION BY employee) DESC,
          company,
          department,
          employee;

Answer 1

In your example dataset, departments are unique, so you can:

PARTITION BY department

However, if more than one company can have the same department, you'd need:

PARTITION BY company, department

---

Side note: since you're grouping on company, department, employee the third over clause is superfluous. You could simplify by omitting it:

ORDER  BY SUM(SUM(sales)) over (PARTITION BY company) DESC
,         SUM(SUM(sales)) over (PARTITION BY company, department) DESC
,         SUM(sales) DESC