In this article we will discuss how to calculate the sum of result query in SQL server. Suppose you have a table (tbl_product) with columns (productId, productName, price and qty) as follows:
Let's say you want to calculate the total price of each product (i.e., price of the product X quantity of product), you need to write an SQL Query to achieve this as mentioned below.
SELECT productId, productName, price, qty, price*Qty 'Total' FROM tbl_product
The output for the above mentioned SQL Query will be as follows:
Now if you want to calculate the gross total you need to modify the SQL Query as follows:
SELECT productId, productName, price, qty, price*Qty 'Total', SUM(price*Qty) OVER() 'Gross Total' FROM tbl_product
Let's assume that we have the table, "tbl_product" as follows
Now, let us assume that we need to calculate the total price of products based on the brand of the product or name of the product
Now, we’ll see how to calculate the total price of products based on the brand of the product
SELECT product_id, product_name, product_brand, product_price, qty, SUM(product_price * qty) OVER(partition by product_brand) 'Brand wise total' FROM tbl_product
Now, we’ll see how to calculate the total price of products based on the product name of the product
SELECT product_id, product_name, product_brand, product_price, qty, SUM(product_price * qty) OVER(partition by product_name) 'Name wise total' FROM tbl_product