Use Aggregate Function With out Group by:
SQL Over() Function:
The real use of this function is
1. Find Sales ratio of each month sales out of the current year
2. Find Sales ratio of each product sales out of all product
3. Find Sales ratio of each division sales out of all division
Example
1. (SUM(Sales) OVER (PARTITION BY Year,Month) / SUM(Sales) OVER (PARTITION BY Year)) *100
2. (SUM(Sales) OVER (PARTITION BY product) / SUM(Sales) OVER ()) *100
3. (SUM(Sales) OVER (PARTITION BY division) / SUM(Sales) OVER ()) *100
--Example1:
DECLARE @FactSale TABLE(ID INT IDENTITY(1,1),ProductName VARCHAR(50),Amount INT)
INSERT INTO @FactSale(ProductName,Amount)
SELECT 'Samsung',40
UNION
SELECT 'LG',60
UNION
SELECT 'Sony',100
SELECT ProductName
,Amount
,SUM(Amount) OVER () TotalSales
,(CAST(SUM(Amount) OVER (PARTITION BY ProductName) AS NUMERIC(10,2)) / CAST(SUM(Amount) OVER () AS NUMERIC(10,2))) * 100 AS SalesRatiobyProduct
FROM @FactSale
--Example2:
DECLARE @FactSale1 TABLE(ID INT IDENTITY(1,1),Year INT,Month VARCHAR(10),Amount INT)
INSERT INTO @FactSale1(Year,Month,Amount)
SELECT 2008,'Jan',600
UNION
SELECT 2008,'Feb',700
UNION
SELECT 2008,'Mar',200
UNION
SELECT 2008,'Apr',500
UNION
SELECT 2009,'Jan',40
UNION
SELECT 2009,'Feb',60
union
SELECT 2009,'Mar',70
UNION
SELECT 2009,'Apr',30
SELECT Year
,Month
,Amount
,SUM(Amount) OVER () AS TotalSales
,SUM(Amount) OVER (PARTITION BY Year) AS TotalSalesbyYear
,(CAST(SUM(Amount) OVER (PARTITION BY Year,Month) AS NUMERIC(10,2)) / CAST(SUM(Amount) OVER (PARTITION BY Year) AS NUMERIC(10,2))) * 100 AS SalesRatiobyMonth
FROM @FactSale1
Please go through this for more information
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/ddcef3a6-0341-43e0-ae73-630484b7b398.htm
