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!

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;

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;

Steven Willis (3/5/2013)

---

Another old habit to break if you are using SQL2008 or greater is to avoid GROUP BY when all you really need is an aggregated column value.
...
You can still use GROUP BY of course if you need to group the portfolios themselves but just to do sums or counts (or min or max, etc) the OVER() clause makes things a lot easier.
 

calvo (3/5/2013)

---

Out of curiosity, why avoid GROUP BY? Do you suggest this only because it is "easier"?

Steven Willis (3/5/2013)

---

Well, yes, to a degree I find it easier to maintain and more readable if it will do the job.

But sometimes you may want to SUM on one grouping and COUNT on a different grouping. You can do that using the OVER() clause but it would get complicated when the only grouping tool available is GROUP BY.

How many times have you received the error message: "Column 'dbo.TableName.ColName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."