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?
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;