Get sum by multiple groups with 1 select-CodePudding

There is a table that looks like this:

----------------------------------------
|Id | Category   | Subcategory | Price |
----------------------------------------
| 1 | Vegetables |   Potatoes  |  100  |
| 2 | Vegetables |   Potatoes  |  200  |
| 3 | Vegetables |   Tomatoes  |  500  |
----------------------------------------

I'd love to get both the total price of each category and total price of each subcategory. Just like this:

-----------------------------------------------------------------------------
| Category   | Subcategory | Total Category Price | Total Subcategory Price |
-----------------------------------------------------------------------------
| Vegetables |   Potatoes  |  800                 | 300                     |
| Vegetables |   Tomatoes  |  800                 | 500                     |
-----------------------------------------------------------------------------

How do I achieve it? This is where I got so far:

SELECT
    Category,
    Subcategory,
    SUM(Price) as [Total Category Price]
    SUM(Price) as [Total Subcategory Price]
FROM 
    [table] 
GROUP BY
    Category,
    Subcategory

CodePudding user response：

The easiest way is to use a correlated subquery for the total per category:

select
  Category,
  Subcategory,
  (select Sum(Price) from t t2 where t2.Category=t.Category) [Total Category Price],
  Sum(Price) as [Total Subcategory Price]
from t
group by Category, Subcategory

Another way is to use window functions for the total

with x as (
  select *, Sum(Price) over(partition by category) [Total Category Price]
  from t
)
select category, subcategory, [Total Category Price],
  Sum(price) [Total subcategory Price]
from x
group by Category, Subcategory, [Total Category Price]

CodePudding user response：

You can use a window function.

Note that the SUM aggregation function is placed inside another SUM window function

SELECT
    Category,
    Subcategory,
    SUM(SUM(Price)) OVER (PARTITION BY Category) as [Total Category Price]
    SUM(Price) as [Total Subcategory Price]
FROM 
    [table] 
GROUP BY
    Category,
    Subcategory

CodePudding user response：

You can try this :

   SELECT Category,
       Subcategory,
       (SELECT SUM(Price) FROM [table]) AS TotalCategoryPrice
    SUM(Price) AS [Total Category Price]
    SUM(Price) AS [Total Subcategory Price]
FROM [table]
GROUP BY Category, Subcategory

CodePudding user response：

There may be different categories, so you can use SUM function with OVER-PARTITION function, like this:

IF(OBJECT_ID('tempdb..#t') IS NOT NULL)
BEGIN
    DROP TABLE #t
END

CREATE TABLE #t
    (
    id int,
    category varchar(100),
    subcategory varchar(100),
    price float
    )

INSERT INTO #t 
VALUES
    (1, 'Vegetables', 'Potatoes', 100),
    (2, 'Vegetables', 'Potatoes', 200),
    (3, 'Vegetables', 'Tomatoes', 500),
    (4, 'Fruit', 'Apple', 300),
    (5, 'Fruit', 'Apple', 400),
    (6, 'Fruit', 'Banana', 600)


SELECT
    t.category AS [Category],
    t.subcategory AS [Subcategory],
    SUM(SUM(t.price)) OVER (PARTITION BY t.category) AS [Total Category Price],
    SUM(t.price) AS [Total Subcategory Price]
FROM #t t
GROUP BY
    t.category,
    t.subcategory

CodePudding user response：

You can make it with sub query

 select Category, Subcategory,
       (Select sum(Price) From [Table] Where Category=t.Category) As [Total Category Price],
       Sum(Price) As Subcategory
       From [Table] t
       Group by Category, SubCategory

CodePudding user response：

I would prefer to go with co-related subquery to calculate "Total Category Price".

---SOLUTION

SELECT
    Category
    , Subcategory
    , (SELECT SUM(sq.Price) FROM [table] sq WHERE sq.Category = t.Category ) AS [Total Category Price]
    , SUM(Price) AS [Total Subcategory Price]
FROM [table] t
GROUP BY
    Category
    , Subcategory

CodePudding user response：

I have tried in a different way using Recursive CTE's. Using CTE's gives us scope in many directions.Hope this helps too.

;WITH SubCategory_cte AS(
SELECT t.category,t.subcategory,SUM(t.price) AS TotalSubCategoryPrice FROM t
GROUP BY t.subcategory,t.category),Category_cte AS(SELECT SUM(t.price) AS TotalCategoryPrice FROM t GROUP BY T.category)

SELECT SubCategory_cte.*,Category_cte.*, from SubCategory_cte 
CROSS JOIN Category_cte;

Query to find % share of sub-category in a category:

;WITH SubCategory_cte AS(
SELECT t.category,t.subcategory,SUM(t.price) AS TotalSubCategoryPrice FROM t
GROUP BY t.subcategory,t.category),Category_cte AS(SELECT SUM(t.price) AS TotalCategoryPrice FROM t GROUP BY T.category)

SELECT SubCategory_cte.*,Category_cte.* ,((TotalSubCategoryPrice/TotalCategoryPrice)*100) AS PercentageShare from SubCategory_cte
CROSS JOIN Category_cte