Find best seller product based on sale and quantity

Transact-SQL
1

Hi,
using sql 2012 version and would like to get the best sale product listing sorted based on sale and qty.
If a best sale product don't have stock,it should go behind the list

DECLARE @Test TABLE
(
Product varchar(10)
,Price int
,StockAvailable int
)
INSERT INTO @Test
VALUES
(
'Apple'
,200
,40
),
(
'Apple'
,200
,40
),
(
'Orange'
,400
,10
),
(
'Orange'
,400
,10
),
(
'Grape'
,500
,0
),
(
'Grape'
,500
,0
),
(
'Plum'
,250
,10
),
(
'Plum'
,250
,10
)

Expected result

Product Sale StockAvailable

Orange 800 10
Plum 500 10
Apple 400 40
Grape 1000 0

Tried using below query,but it gave the out of stock product first in the result
select
distinct Product
,sum(price) as Sale
,StockAvailable
from
@Test
group by Product
,StockAvailable
order by Sale desc,
StockAvailable

2

I am not able to correlate your expected result to the data from your sample table.

For example, you have two rows for product = Orange, each with Price = 400 and Stock Available = 10. In your output you want to get one row for product = Orange, with Sale = 8 and Stock Available = 10. How did you arrive at those numbers?

3

Sorry for the typo,corrected as 800

4

I must admit that the logic you are trying to implement does not make sense to me. When I run the sample data that you posted, this is what I see.

In your result, you want the prices to be added (which is rather unusual, unless you mean cost or proceeds rather than price) yet you pick one of the values from the StockAvailable. If for a given Product, the StockAvailable is not the same on all rows, which row do you want to pick?

Regardless, for your sample data, the following query will give you the desired output.

SELECT
	Product,
	SUM(Price) AS Price,
	MAX(StockAvailable) AS StockAvailable
FROM
	@Test
GROUP BY
	Product
ORDER BY
	CASE WHEN MAX(StockAvailable) = 0 THEN 1 ELSE 0 END,
	SUM(Price) DESC;
1 Like