Complex Query question. Using Sum and CAST operation.

Hi

Can onayone tell me what is wrong with the below code. Thank you,

SELECT P.FirstName, P.LastName, P.PortfolioID, P.PortfolioDescription, SC.Description, SC.SecurityID, SC.SecurityTypeID, SUM(T.Quantity) AS TQuantity,
CASE SC.SecurityTypeID WHEN 11 THEN CAST(SUM(T .Quantity)) WHEN 2 THEN CAST(SUM(T .Quantity *
(SELECT Price
FROM dbo.SecurityPrices AS SP WITH (Nolock)
WHERE T .SymbolID = SP.SecurityID AND PriceDate = '1/24/2007'))) / 100 ELSE CAST(SUM(T .Quantity *
(SELECT Price
FROM dbo.SecurityPrices AS SP WITH (Nolock)
WHERE T .SymbolID = SP.SecurityID AND PriceDate = '1/24/2007'))) END AS Amount, P.AccountNumber, P.AccountTypeDescription,
SC.CodeDescription, SC.CodeDescription2, SC.Symbol
FROM dbo.Transactions AS T WITH (Nolock) INNER JOIN
dbo.Portfolios AS P WITH (Nolock) ON P.PortfolioID = T.PortfolioID INNER JOIN
dbo.SFGSecuritiesAndCodes AS SC WITH (Nolock) ON T.SymbolID = SC.SecurityID INNER JOIN
dbo.Groups AS G WITH (Nolock) ON P.PortfolioID = G.PortfolioID
WHERE (T.StatusTypeID = 100) AND (G.OwningPortfolioID = 270) AND (T.TradeDate <= '1/24/2007')
GROUP BY T.SymbolID, P.FirstName, P.LastName, P.PortfolioID, SC.Description, SC.SecurityID, SC.SecurityTypeID, P.PortfolioDescription, P.AccountNumber,
P.AccountTypeDescription, SC.CodeDescription, SC.CodeDescription2, SC.Symbol

The correct syntax for CAST is CAST( <expr> as <datatype>). So fix your query and try again. Also, it is good to post the actual error message that you are getting so that we don't have to guess what might be wrong.|||

The following query will work for you..

Note: You Cannot perform an aggregate function on an expression containing an aggregate or a subquery; So you have to use INNER JOIN Instead of Subquery. It is good practice to use join rather than the subquery.

SELECT
P.FirstName,
P.LastName,
P.PortfolioID,
P.PortfolioDescription,
SC.Description,
SC.SecurityID,
SC.SecurityTypeID,
SUM(T.Quantity) AS TQuantity,
CASE SC.SecurityTypeID
WHEN 11 THEN
CAST(SUM(T .Quantity) As Int)
WHEN 2 THEN
CAST(SUM(T .Quantity * SP.Price) / 100 as INT)
ELSE
CAST(SUM(T .Quantity * SP.Price) as INT)
END AS Amount,
P.AccountNumber,
P.AccountTypeDescription,
SC.CodeDescription,
SC.CodeDescription2,
SC.Symbol
FROM
dbo.Transactions AS T WITH (Nolock)
INNER JOIN dbo.Portfolios AS P WITH (Nolock) ON P.PortfolioID = T.PortfolioID
INNER JOIN dbo.SFGSecuritiesAndCodes AS SC WITH (Nolock) ON T.SymbolID = SC.SecurityID
INNER JOIN dbo.Groups AS G WITH (Nolock) ON P.PortfolioID = G.PortfolioID
INNER JOIN dbo.SecurityPrices As SP WITH (Nolock) ON T.SymbolID = SP.SecurityID AND SP.PriceDate = '1/24/2007'
WHERE
(T.StatusTypeID = 100)
AND (G.OwningPortfolioID = 270)
AND (T.TradeDate <= '1/24/2007')
GROUP BY
T.SymbolID,
P.FirstName,
P.LastName,
P.PortfolioID,
SC.Description,
SC.SecurityID,
SC.SecurityTypeID,
P.PortfolioDescription,
P.AccountNumber,
P.AccountTypeDescription,
SC.CodeDescription,
SC.CodeDescription2,
SC.Symbol