How to use multiple partitions in PostgreSQL-CodePudding

How do we output columns based on the count of the business. If businesses are tied frequency shown, we order the business with the most items in stock SUM(Inventory) - SUM(Total Sold)
Given the following table

Business      | Product   | Total_Sold |  Inventory
---------------------------------------------------
Jane's        | Shoes     |  10        |    30 
Jane's        | Top       |  20        |    14 
Jane's        | Top       |  20        |    21 
Smith's       | Bottom    |  50        |    30 
Kamp's        | Shoes     |  20        |    40 
Kamp's        | Top       |  40        |    50 
Kamp's        | Bottom    |  50        |    70

Output

Business      | Product
------------------------
Kamp's        | Shoes  
Kamp's        | Top     
Kamp's        | Bottom   
Jane's        | Shoes     
Jane's        | Top       
Jane's        | Top      
Smith's       | Bottom

Where Kamp's will be shown first as it appears the most and has the most items in stock (70). Jane's will come second as it also appears 3 times but it only has 15 items in stock.

The below query returns the Jane's before Kamp's as Jane's appears different in table.

SELECT business, product
FROM  (
   SELECT business, product
        , count(*) OVER (PARTITION BY business) AS ct
   FROM TABLE
   ) sub
ORDER  BY ct DESC, business, product;

The below query calculates to in stock total

SELECT business, SUM(total_sold)-SUM(inventory) as diff
FROM TABLE
GROUP by business
ORDER by COUNT(distinct product) DESC, diff ASC

Can a new partition be added to handle the group by and combine these queries together?

CodePudding user response：

You can use window function :

select *
from(select *, count(*) over (partition by business) as businesscnt,
               sum(inventory-total_sold) over(partition by business) as closingqty
     from t
     ) t
order by businesscnt desc, closingqty desc;