Ranking and Summing
-
Any help would be appreciated.
I need to sum per product type and then I need to also sum depending on whether the product ranking is good or bad.
I could probably do this with multi-stage temporary table type processing but I imagine a single select could it as well. I just can't imagine the specifics.
So total fruit sales was 139. Good fruit sales was 84. So 60% of fruit sales was good, 40 bad.
Desired results set is like this:
Fruits Good Grade60
Fruits Bad Grade40
VegetablesGood Grade80
VegetablesBad Grade20
Dairy Good Grade38
Dairy Bad Grade61
CREATE TABLE #Products
(
ProductID int,
ProductType char(10),
ProductSales int,
ProductRank int
)
INSERT INTO #Products (ProductID, ProductType,ProductSales,ProductRank) VALUES (1,'Fruits',55,3)
INSERT INTO #Products (ProductID, ProductType,ProductSales,ProductRank) VALUES (2,'Fruits',57,5)
INSERT INTO #Products (ProductID, ProductType,ProductSales,ProductRank) VALUES (3,'Fruits',27,6)
INSERT INTO #Products (ProductID, ProductType,ProductSales,ProductRank) VALUES (4,'Vegetables',67,1)
INSERT INTO #Products (ProductID, ProductType,ProductSales,ProductRank) VALUES (5,'Vegetables',46,5)
INSERT INTO #Products (ProductID, ProductType,ProductSales,ProductRank) VALUES (6,'Vegetables',73,5)
INSERT INTO #Products (ProductID, ProductType,ProductSales,ProductRank) VALUES (7,'Vegetables',124,9)
INSERT INTO #Products (ProductID, ProductType,ProductSales,ProductRank) VALUES (8,'Vegetables',27,6)
INSERT INTO #Products (ProductID, ProductType,ProductSales,ProductRank) VALUES (9,'Dairy',5,7)
INSERT INTO #Products (ProductID, ProductType,ProductSales,ProductRank) VALUES (10,'Dairy',97,3)
INSERT INTO #Products (ProductID, ProductType,ProductSales,ProductRank) VALUES (11,'Dairy',56,8)
SELECT
CASE
WHEN ProductRank >= 5 THEN 'Good Grade'
WHEN ProductRank < 5 THEN 'Bad Grade'
END AS Grade,
*
FROM #Products
DROP TABLE #Products
-
sorry,I cant understand your doubt but i think this may provide solution to you.
Declare @product ID int
Declare @product Type char(10)
Declare @product Rank int
Declare cc cursor
for
select Product Type,Product Rank from #Products
open cc
fetch next from cc into @product Type,@Product Rank
While @@FETCH_STATUS = 0
begin
select Count(Product Type) from #Products where Product Type=@Product Type and Product Rank>=5
fetch next from cc into @product Type,@Product Rank
end
close cc
Deal locate cc
-
WITH CTE AS (SELECT ProductType,
SUM(CASE WHEN ProductRank >= 5 THEN ProductSales ELSE 0 END) AS GoodGrade,
SUM(CASE WHEN ProductRank < 5 THEN ProductSales ELSE 0 END) AS BadGrade,
SUM(ProductSales) AS Total
FROM #Products
GROUP BY ProductType)
SELECT ProductType,
Grade,
(Value*100)/Total AS [Percent]
FROM CTE
CROSS APPLY (VALUES('Good Grade',GoodGrade) , ('Bad Grade' ,BadGrade)) x(Grade,Value);
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Another Sol :
Select ProductType,
Grade,
(Value*100)/productsales AS [Percent]
FRom
(
select ProductType ,Sum(case when ProductRank >= 5 then productsales END) Goodproductsales ,
Sum(case when ProductRank < 5 then productsales END) BAdproductsales,
Sum(productsales) productsales
From #Products
Group by ProductType
)Data
CROSS APPLY (VALUES('Good Grade',Goodproductsales) , ('Bad Grade' ,BAdproductsales)) x(Grade,Value);
Neeraj Prasad Sharma
Sql Server Tutorials -
Thanks, interesting. I wouldn't have thought to use cross apply.
-
Chrissy321 (6/14/2013)
Thanks, interesting. I wouldn't have thought to use cross apply.There's a good article about it here[/url]
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply