CODEWITHSUNDEEP
t-sqlsql-server-2012aggregatepartition
eek
eek

Reputation: 724

Can I use PARTITION BY with a GROUP BY clause? SQL Server 2012

I'm new to windowed functions. Here is the original table with 4 types of fruit.

fruit   quantity
orange  100
banana  27
banana  20
orange  5
melon   5
apple   1
banana  10
banana  4
banana  36
banana  86
banana  47
apple   32
banana  7
banana  5
banana  3

Is it possible to turn this into the percentage each fruit makes up of the total quantity for EVERYTHING? Here is what I want:

fruit       total       percentage
apple       33          9%
banana      245         63%
orange      105         27%
melon       5           1%

Here is the code I'm trying out, but it's giving me an error:

SELECT fruit
, SUM(quantity) / SUM(quantity) OVER () * 100
FROM fruit_inventory
GROUP BY fruit

If I undo the GROUP BY and remove the first SUM(quantity) then I get multiple records that look like this:

fruit   quantity    percentage
apple   1           0%
apple   32          8%
banana  27          7%
banana  20          5%
banana  10          3%
banana  4           1%
banana  36          9%
banana  86          22%
banana  47          12%
banana  7           2%
banana  5           1%
banana  3           1%
melon   5           1%
orange  100         26%
orange  5           1%

Upvotes: 2

Views: 93

Answers (3)

Mustafa Erdogan
Mustafa Erdogan

Reputation: 113

Using CTE (Common Table Expression) you can have the results like you wanted. Overall CTE's do have better performance and are better readable.

WITH cte AS
(
    SELECT DISTINCT 
        Fruit
        ,SUM(Quantity) OVER (PARTITION BY fruit ORDER BY fruit) AS sumProducts
        ,SUM(SUM(Quantity)) OVER ()  AS totalProducts
    FROM stackOverflow
    GROUP BY 
        Fruit
        ,Quantity
)
SELECT Fruit, CAST(sumProducts AS INT) AS sumProducts, CAST(100 * sumProducts / totalProducts AS DECIMAL(5,2)) AS percentage
FROM cte

Upvotes: 0

Ralph
Ralph

Reputation: 9434

I believe this is what you're looking for:

declare @fruitbasket table
    (
     Fruit nvarchar(50),
     Quantity decimal(19, 5)
    );

insert  into @fruitbasket
        (Fruit, Quantity)
values  (N'orange', 100),
        (N'banana', 27),
        (N'banana', 20),
        (N'orange', 5),
        (N'melon', 5),
        (N'apple', 1),
        (N'banana', 10),
        (N'banana', 4),
        (N'banana', 36),
        (N'banana', 86),
        (N'banana', 47),
        (N'apple', 32),
        (N'banana', 7),
        (N'banana', 5),
        (N'banana', 3);

select  Fruit,
        sum(Quantity) as Quantity,
        sum(Quantity) / (
                         select sum(Quantity) as Total
                         from   @fruitbasket
                        ) * 100 as PercentageOfTotal
from    @fruitbasket
group by Fruit;

Upvotes: 2

Shannon Severance
Shannon Severance

Reputation: 18410

Use a windowed sum of the aggregate sum.

SELECT  Fruit ,
        SUM(Quantity) AS Quantity ,
        SUM(Quantity) / SUM(SUM(Quantity)) OVER () * 100
FROM    @fruitbasket
GROUP BY Fruit;

Upvotes: 3

Related Questions