meadow0 (3/5/2013)
Hi Guys,I'll basically do my best to describe what I'm trying to accomplish, and give you the query I have so far (which isn't correct).
I have several different tables. In this case, three: ERTutAccounts, ERTutPositions, and ERTutSecMast.
What I'm trying to accomplish is a table where one result set lives. Within this table I'd ideally like to have:
Portfolio | PortfolioID | # Of Securities | Total Market Value of Portfolio
I've gotten as far as Column 1, Column 2, and Column 4, but am unable to add the 3rd column (counting the # of securities in each portfolio).
My query is as follows:
SELECT ERTutAccounts.Portfolio, ERTutPositions.PortfolioID, SUM(ERTutPositions.MarketValue) AS SumOfMV
From ERTutAccounts, ERTutPositions
WHERE ERTutPositions.PortfolioID=ERTutAccounts.PortfolioID
GROUP BY ERTutAccounts.Portfolio, ERTutPositions.PortfolioID
This query above does the job, but when I add in the Securities...
SELECT ERTutAccounts.Portfolio, ERTutPositions.PortfolioID, SUM(ERTutPositions.MarketValue) AS SumOfMV, ERTutSecMast.SecID AS '# of Securities'
From ERTutAccounts, ERTutPositions, ERTutSecMast
WHERE ERTutPositions.PortfolioID=ERTutAccounts.PortfolioID
AND ERTutPositions.SecID=ERTutSecMast.SecID
GROUP BY ERTutAccounts.Portfolio, ERTutPositions.PortfolioID, ERTutSecMast.SecID
When I do this, it will list each SecID individually and repeat the PortfolioID over and over again.
I understand this is confusing, and I attempted to make it concise as possible. As you can tell, I'm an extreme newbie who has been using SQL for about three days now. Any input is appreciated!
You should get out of the habit of using the old style join syntax and instead use the new syntax.
Here's a total guess for what you want, as you haven't supplied sample data or DDL (see link in my signature about the best way to ask a question) it's hard to be sure.
SELECT A.Portfolio, B.PortfolioID, SUM(B.MarketValue) AS SumOfMV,
[# of Securities]
From ERTutAccounts A
INNER JOIN ERTutPositions B ON A.PortfolioID = B.PortfolioID
CROSS APPLY (SELECT COUNT(*)
FROM ERTutSecMast
WHERE B.SecID = SecID) C([# of Securities])
GROUP BY ERTutAccounts.Portfolio, ERTutPositions.PortfolioID;
Edited: Thinking about it, you probably don't need that third table. Try this: -
SELECT A.Portfolio, B.PortfolioID, SUM(B.MarketValue) AS SumOfMV,
COUNT(B.SecID) AS [# of Securities]
From ERTutAccounts A
INNER JOIN ERTutPositions B ON A.PortfolioID = B.PortfolioID
GROUP BY ERTutAccounts.Portfolio, ERTutPositions.PortfolioID;