Group by that shows the percentage in the specific date

I have a table with the following columns:

StockID,FundID,DateID,ValueInDate

I want to Create a GROUP BY Query that shows the Percentage Of every Fund From The Total Value in the sp开发者_开发百科ecific Date, Without Parameters.

I have the @DateID Parameter only in this Query

Any ideas?

You may want to use a derived table (uncorrelated subquery) to get the total of the values for a particular @DateID. Consider the following example:

SELECT  t.*, (t.ValueInDate / dt.sum_value) * 100 as perc
FROM    your_table t
JOIN    (
            SELECT   SUM(ValueInDate) sum_value, DateId
            FROM     your_table
            WHERE    DateID = @DateID
            GROUP BY DateId
        ) dt ON (dt.DateID = t.DateID)
WHERE   t.DateID = @DateID;

Test case:

CREATE TABLE your_table (
   StockID int, FundID int, DateID int, ValueInDate decimal(10,2)
);

INSERT INTO your_table VALUES (1, 1, 1, 35);
INSERT INTO your_table VALUES (2, 1, 1, 75);
INSERT INTO your_table VALUES (3, 2, 1, 25);
INSERT INTO your_table VALUES (4, 2, 1, 50);
INSERT INTO your_table VALUES (5, 3, 2, 15);
INSERT INTO your_table VALUES (6, 3, 2, 25);
INSERT INTO your_table VALUES (7, 4, 2, 30);
INSERT INTO your_table VALUES (8, 4, 2, 60);

Result when @DateID = 1:

+---------+--------+--------+-------------+-----------+
| StockID | FundID | DateID | ValueInDate | perc      |
+---------+--------+--------+-------------+-----------+
|       1 |      1 |      1 |       35.00 | 18.918919 |
|       2 |      1 |      1 |       75.00 | 40.540541 |
|       3 |      2 |      1 |       25.00 | 13.513514 |
|       4 |      2 |      1 |       50.00 | 27.027027 |
+---------+--------+--------+-------------+-----------+
4 rows in set (0.00 sec)

This is the solution - in the solution below the total sum is not for each Fund, its for all fund together. i fix it a little bit, thanks for Daniel Vassallo!!

DECLARE @DATEID int
SET @DATEID=1

SELECT  t.FundID,t.ValueInDate / CAST(dt.sum_value AS FLOAT) as perc,dt.sum_value
FROM    tbl_Holding_Gemel t JOIN tbl_Funds tf ON t.FundID = tf.FundID

JOIN    (       
            SELECT   SUM(ValueInDate) sum_value, FundID 
            FROM     tbl_Holding_Gemel 
            WHERE    DateID = @DateID 
            Group By FundID            
        ) dt ON (dt.FundID = t.FundID) 
WHERE   t.DateID = @DateID