Unable to use PARTITION BY with COUNT(*) when multiple columns are used

Question

Suppose I have a table with the following columns:

• TYPE
• DEPARTMENT
• SUPPLIER
• ORDER
• TOTAL

And I wanted to query this data so that I get ordered results, first grouped by TYPE. The order being the Number of Orders. Then the following Query Works Well for me (http://sqlfiddle.com/#!15/78cc1/1):

WITH company_sales(type, department, supplier, order_number, total) AS (
   VALUES
     ('Edibles'    , 'Department-1', 'Supplier-1' , 'ORDER-1' ,   10)
   , ('Edibles'    , 'Department-1', 'Supplier-2' , 'ORDER-2' ,   20)
   , ('Edibles'    , 'Department-1', 'Supplier-3' , 'ORDER-3' ,   30)
   , ('Edibles'    , 'Department-1', 'Supplier-4' , 'ORDER-4' ,   40)
   , ('Edibles'    , 'Department-2', 'Supplier-5' , 'ORDER-5' ,   50)
   , ('Edibles'    , 'Department-2', 'Supplier-6' , 'ORDER-6' ,   60)
   , ('Edibles'    , 'Department-3', 'Supplier-7' , 'ORDER-7' ,   70)
   , ('Edibles'    , 'Department-3', 'Supplier-8' , 'ORDER-8' ,   80)
   , ('Edibles'    , 'Department-3', 'Supplier-9' , 'ORDER-9' ,   90)
   , ('Edibles'    , 'Department-3', 'Supplier-9' , 'ORDER-10',  100)
   , ('Edibles'    , 'Department-4', 'Supplier-10', 'ORDER-11',  110)
   , ('Non-Edibles', 'Department-2', 'Supplier-11', 'ORDER-12', 1000)
   , ('Non-Edibles', 'Department-3', 'Supplier-12', 'ORDER-13', 1010)
   , ('Non-Edibles', 'Department-3', 'Supplier-13', 'ORDER-14', 1020)
   , ('Non-Edibles', 'Department-3', 'Supplier-14', 'ORDER-15', 1030)
   , ('Non-Edibles', 'Department-3', 'Supplier-14', 'ORDER-16', 1040)
   , ('Non-Edibles', 'Department-4', 'Supplier-15', 'ORDER-17', 1050)
)
SELECT cs.type,
       count(*)   sum_total_count,
       sum(total) sum_grand_total
FROM   company_sales cs
GROUP  BY cs.type
ORDER  BY Sum(Count(*)) OVER (partition BY cs.type) DESC,
          cs.type ASC;

And if I wanted to query this data so that I get ordered results, first grouped by TYPE and then by DEPARTMENT. The order being the Number of Orders. Then the following Query Works Well for me (http://sqlfiddle.com/#!15/78cc1/2):

WITH company_sales(type, department, supplier, order_number, total) AS ( ...)
SELECT cs.type,
       cs.department,
       count(*)   sum_total_count,
       sum(total) sum_grand_total
FROM   company_sales cs
GROUP  BY cs.type,
          cs.department
ORDER  BY Sum(Count(*)) OVER (partition BY cs.type) DESC,
          Sum(Count(*)) OVER (partition BY cs.type, cs.department) DESC,
          cs.type ASC,
          cs.department ASC;

However, following the same pattern when I want ordered results, first grouped by TYPE and then by DEPARTMENT, and then by SUPPLIER, with the order being the Number of Orders. Then the following Query DOES NOT WORK for me (http://sqlfiddle.com/#!15/78cc1/3):

WITH company_sales(type, department, supplier, order_number, total) AS (...)
SELECT cs.type,
       cs.department,
       cs.supplier,
       count(*)   sum_total_count,
       sum(total) sum_grand_total
FROM   company_sales cs
GROUP  BY cs.type,
          cs.department,
          cs.supplier
ORDER  BY Sum(Count(*)) OVER (partition BY cs.type) DESC,
          Sum(Count(*)) OVER (partition BY cs.type, cs.department) DESC,
          Sum(Count(*)) OVER (partition BY cs.type, cs.department, cs.supplier) DESC,
          cs.type ASC,
          cs.department ASC,
          cs.supplier ASC;

The above query results in the following:

Whereas, I desire the following:

Where am I going wrong?

Answer 1

Two preliminaries:

1. NEVER EVER use SQL reserved key words for column names. In what follows type -> typ and order -> ordnum.
2. Learn to use the VALUES clause. I suggest you edit your question according to the below example and no need to repeat 3 times (just use something like WITH company_sales (...) AS (...) SELECT ...; that's how the PG manual does it, for instance). As it is, your question is way too verbose and people will not read it through. (OK - Erwin Brandstetter edited this away from the OP)

First

The query returns results out of the order that you want because you specify your ORDER BY clauses in the wrong order. Under typ = 'Edibles' both department 1 and 3 have 4 orders. Analyzing your ORDER BY clause we see the following:

      -- 1. This orders by the type with the most orders - OK
ORDER BY Sum(Count(*)) OVER (PARTITION BY cs.type) DESC,
      -- 2. Number of orders by department within each type - OK but tie #1 and #3
         Sum(Count(*)) OVER (PARTITION BY cs.type, cs.department) DESC,
      -- 3. Number of orders by supplier with each type, department - NOT OK
         Sum(Count(*)) OVER (PARTITION BY cs.type, cs.department, cs.supplier) DESC,
      -- 4. In case of ties, order by type name - OK
         cs.type ASC,
      -- 5. In case of ties, order by department name - OK
         cs.department ASC,
      -- 6. In case of ties, order by supplier name - OK
         cs.supplier ASC;

So why is #3 NOT OK? In fact, #3 is quite fine but #4, #5 and #6 are in the wrong place. You want to order by numbers of types, then departments, then suppliers and resolve ties alphabetically. You should resolve those ties immediately after every ordering on numbers of rows (with removal of obsolete table alias and ASC clause):

ORDER BY
  Sum(Count(*)) OVER (PARTITION BY typ) DESC, typ,
  Sum(Count(*)) OVER (PARTITION BY typ, department) DESC, department,
  Sum(Count(*)) OVER (PARTITION BY typ, department, supplier) DESC, supplier;

So why was your ordering wrong? Well, departments 1 and 3 within 'Edibles' each have 4 rows so they are tied. The following clause orders supplier 9 with 2 orders above the other suppliers with 1 order each. Ordering by department name before supplier orders breaks the tie between the departments and all of your rows order just fine.

Then

The clause Sum(Count(*)) OVER (PARTITION BY ...) is nonsense. count(*) assigns to each row in the partition a new column with the number of rows in that partition. Summing that up again gives you the square which has exactly the same ordering properties as the basic number of rows.

Furthermore, Sum(Count(*)) OVER (PARTITION BY typ, department, supplier) is useless because you already GROUP BY these same columns to produce count(*) in the select list.

Gives

Putting this all together and using positional parameters for brevity we then get:

WITH company_sales(typ, department, supplier, ordnum, total) AS (...)
SELECT typ,
       department,
       supplier,
       count(*) sum_total_count,
       sum(total) sum_grand_total
FROM   company_sales
GROUP  BY 1, 2, 3
ORDER BY
      count(*) OVER (PARTITION BY typ) DESC, 1,
      count(*) OVER (PARTITION BY typ, department) DESC, 2,
      4 DESC, 3;

Resulting in:

     typ     |  department  |  supplier   | sum_total_count | sum_grand_total
-------------+--------------+-------------+-----------------+-----------------
 Edibles     | Department-1 | Supplier-1  |               1 |              10
 Edibles     | Department-1 | Supplier-2  |               1 |              20
 Edibles     | Department-1 | Supplier-3  |               1 |              30
 Edibles     | Department-1 | Supplier-4  |               1 |              40
 Edibles     | Department-3 | Supplier-9  |               2 |             190
 Edibles     | Department-3 | Supplier-7  |               1 |              70
 Edibles     | Department-3 | Supplier-8  |               1 |              80
 Edibles     | Department-2 | Supplier-5  |               1 |              50
 Edibles     | Department-2 | Supplier-6  |               1 |              60
 Edibles     | Department-4 | Supplier-10 |               1 |             110
 Non-Edibles | Department-3 | Supplier-12 |               1 |            1010
 Non-Edibles | Department-3 | Supplier-13 |               1 |            1020
 Non-Edibles | Department-3 | Supplier-14 |               2 |            2070
 Non-Edibles | Department-2 | Supplier-11 |               1 |            1000
 Non-Edibles | Department-4 | Supplier-15 |               1 |            1050
(15 rows)