group by query with sum all value

A more compact/professional way, should be faster too because it doesn't require join:

SELECT 
  productID,
  Type,
  SUM(Amount) AS TotalTypeAmount,
  SUM(SUM(Amount)) OVER (PARTITION BY productID) as TotalPerProduct
FROM 
  Product
GROUP BY 
  productID,Type

http://sqlfiddle.com/#!6/ca308/4

SUM(amount) is the SUM of amount per productid,type i.e. the SUM you would get if you just did the GROUP BY so you'll have multiple different sums per productid because they're broken down into type subgroups. SUM(SUM(amount)) PARTITION BY(productid) is "the sum of sum_per_productid_and_type grouped by productid only"

Suppose we had the simpler query:

SELECT 
  productID,
  Type,
  SUM(Amount) AS TotalTypeAmount
FROM 
  Product
GROUP BY 
  productID,Type

This might produce results like:

id1, type1, 100
id1, type2, 200
id2, type1, 300
id2, type2, 400

We can see that the total for all id1 would be 300, and the total for all id2 would be 700. We can introduce a window function to sum all the data by productid only, and it would produce results where the sum repeats, rather than causing the number of rows to shrink, i.e.:

id1, type1, 100, 300
id1, type2, 200, 300
id2, type1, 300, 700
id2, type2, 400, 700

This is because window functions do a grouping/summing but then apply the result to every row.

It is important to understand that a window operation is performed AFTER the grouping and summing is done, but BEFORE we have given the columns any aliases in the SELECT. If the column names HAD been assigned before the window function was run then we could have said SUM(TotalTypeAmount) instead of SUM(SUM(Amount)):

SELECT 
  productID,
  Type,
  SUM(Amount) AS TotalTypeAmount,
  SUM(TotalTypeAmount) OVER (PARTITION BY productID) as TotalPerProduct
FROM 
  Product
GROUP BY 
  productID,Type

But this is a syntax error, because we can't refer to TotalTypeAmount in the same SELECT as we define it. We could make this happen by using a subquery though:

SELECT
  x.productID,
  x.Type,
  x.TotalTypeAmount, --here we use it after it has been defined in the subquery
  SUM(x.TotalTypeAmount) OVER (PARTITION BY x.productID) as TotalPerProduct
FROM
(
  SELECT 
    productID,
    Type,
    SUM(Amount) AS TotalTypeAmount --here we define it
  FROM 
    Product
  GROUP BY 
    productID,Type
) x

But it's more complex than it needs to be.

In essence, though it looks weird, we just need to remember that when we see:

SUM(sum(…)) OVER(PARTITION BY …)
…
group by … 

• The inner sum(amount) in lowercase is "the sum that is done by the GROUP BY", and
• The outer SUM(…) OVER(…) in uppercase is "the sum that is done by the window function"

We had to repeat ourselves and say SUM(SUM(amount)) because at the time the window function was being run, the sum done by the group by (i.e. the thing we wanted to total up) didn't have its own name - it was just called SUM(amount)

if you're using SQL Server or Postgres the below should work:

WITH CTE
AS
(
SELECT 
productID,
SUM(Amount) AS TotalAllType
FROM Product
GROUP BY productID
)

SELECT 
productID,
Type,
SUM(Amount) AS TotalTypeAmount
,TotalAllType
FROM Product AS P
LEFT JOIN CTE ON CTE.productID=P.productID
GROUP BY productID,Type, TotalAllType;