complex Query question. Using Sum and CAST operati

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

complex Query question. Using Sum and CAST operati

Author

Topic

V-Nest
Starting Member

10 Posts

Posted - 2007-01-30 : 18:30:51

Hi

Can anyone 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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-30 : 18:38:28

Well can you tell us what the problem is? Are you getting an error? Is it just not returning the correct data? Help us help you by providing more information.

Tara Kizer

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-30 : 18:49:47

Please post your table structure, some sample data & result that you want. Also explain what you want to achieve.

V-Nest
Starting Member

10 Posts

Posted - 2007-01-30 : 19:15:18

Hi,

When I run the code I get "Cannot perform an aggregate function on an expression containing an aggregate or a subquery" error message.

In my database I've got
Portfolios table to store Client information,
Groups table to store portfolioid's if a client have more than one portfolio.
SFGSecuritiesAndCodes table to store stock/fund names,and other information about stocks and funds.
SecurityPrices table to store prices for every stock/fund. The prices gets donwloaded everyday.
Transactions table to store client transactions so that I could keep track of how much or how many stocks/funds they have so far or any given date.

So what I'm trying is to get a financial picture of a given client in a given date. For example I'd like to know for PriceDate 1/24/2007 and for TradeDate 1/24/2007, for a client x, what client X had ( number of stocks, and what its worth).

I hope this makes sense to you. Let me know What else I can provide. Thank you so much.

The table structures are below.


CREATE TABLE [dbo].[Portfolios](
	[PortfolioID] [int] IDENTITY(1,1) NOT NULL,
	[DataSetID] [int] NOT NULL,
	[TargetID] [int] NULL,
	[FirstName] [varchar](40)
	[LastName] [varchar](40) 
	[CompanyName] [varchar](100)
	[Notes] [varchar](100)
)

CREATE TABLE [dbo].[Transactions](
	[TransactionID] [int] IDENTITY(1,1) NOT NULL,
	[PortfolioID] [int] NOT NULL,
	[SymbolID] [int] NULL,
	[Quantity] [float] NOT NULL,
	[NetAmount] [float] NOT NULL,
	[TradeDate] [datetime] NOT NULL 
)

CREATE TABLE [dbo].[Groups](
	[GroupID] [int] IDENTITY(1,1) NOT NULL,
	[PortfolioID] [int] NOT NULL,
	[OwningPortfolioID] [int] NOT NULL,
	[BeginDate] [datetime] NOT NULL,
	[EndDate] [datetime] NOT NULL,
	[CodeID] [int] NULL 
)

CREATE TABLE [dbo].[SecurityPrices](
	[SecurityPriceID] [int] IDENTITY(1,1) NOT NULL,
	[DataSetID] [int] NOT NULL,
	[SecurityID] [int] NOT NULL,
	[PriceDate] [datetime] NOT NULL,
	[Price] [float] NOT NULL 
)

CREATE TABLE [dbo].[ SFGSecuritiesAndCodes](
	[SecurityID] [int] IDENTITY(1,1) NOT NULL,
	[DataSetID] [int] NOT NULL,
	[Description] [varchar][100] NOT NULL,
	[CodeDescription] [varchar][100] NOT NULL,
	[CodeDescription2] [varchar][100] NOT NULL,
	[SecurityTypeID] [int] NOT NULL,
	[Symbol] [varchar][10] NOT NULL 
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-30 : 21:02:43

How about some sample data and the expected result ?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-30 : 22:53:26

why all the nolocks? do you realize you are doing dirty reads when you use that hint?

From the table and column names, this looks like it's for a financial app. So you are doing uncommitted reads on data concerning people's finances.

Can you tell me what company you work for, so I know never to invest my money with that company?

www.elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 23:57:23

Cast sum as what?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 00:13:18

The error message is fairly self-explained.
You cannot do a SUM on a subquery. Also, you have some spaces in the prefixes.

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

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources