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;

Yes, you are right, I realized that the results of the above query are looking good.I must not have a good data set to represent my problem, as the same query in my production dataset (with a different domain) doesn't work.The order of the results are not accurate. — Tora Tora Tora
– Tora Tora Tora, Commented Dec 28, 2015 at 23:00

Andomar · Accepted Answer · 2015-12-28 23:07:07Z

20

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

answered Dec 28, 2015 at 23:07

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tora Tora Tora Over a year ago

Thanks for pointing out that in my example the Departments are UNIQUE. That was key.

Dragas Over a year ago

Does order of columns matter?

skan Over a year ago

Is not the same SUM(SUM(sales)) than SUM(sales)?

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related