I have a table like this:

Assuming multiple products and types, I want to summarize price (Total price) over each product name but have it placed in a separate table like this:

I know there's analytical SQL functions like OVER but I wasn't sure how to make a separate table.

CodePudding user response：

TO make seprate table use this

SELECT Product, SUM(Price) AS Total_price 
INTO schema.newtable FROM schema.ProductTable
Group by Product;

CodePudding user response：

This is a simple aggregation:

select prodct, sum(price) as total_price
from products
group by product
order by product;

And the result is a table. Only it is not a table stored in your database. It is usually not desired to store such an additional table, because then you'd have redundant information in your database, which can lead to inconsistencies later.

But if you want to, you can use above query to create a table:

Update: SQL Server does not support the standard SQL syntax for creating a table from a query. The syntax in SQL Server is:

select prodct, sum(price) as total_price
into new_table
from products
group by product;

CodePudding user response：

The query is just a simple GROUP BY:

SELECT Product, SUM(Price) AS Total_price
FROM Table1
GROUP BY Product;

If you want to insert it into a separate table that already exists, just use an INSERT combined with that query:

INSERT Table2 (Product, Total_price)
SELECT Product, SUM(Price) AS Total_price
FROM Table1
GROUP BY Product;

Or, to create a new table on the fly:

SELECT Product, SUM(Price) AS Total_price
INTO Table2
FROM Table1
GROUP BY Product;