SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Really confused newbie - Perhaps a Temp Table query?


Really confused newbie - Perhaps a Temp Table query?

Author
Message
meadow0
meadow0
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 48
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!
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3872 Visits: 8472
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;




Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
meadow0
meadow0
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 48
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;

[/quote]


This one worked!! Only issue was with the GROUP BY Query. I had to switch the ERTutAccounts to A.Portfolio and ERTutPositions to B.PortfolioID
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 1721
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.



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;






SELECT
A.Portfolio
,B.PortfolioID
,SUM(B.MarketValue) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS SumOfMV
,COUNT(B.SecID) OVER (PARTITION BY A.Portfolio, B.PortfolioID) AS [# of Securities]
FROM
ERTutAccounts A
INNER JOIN ERTutPositions B
ON A.PortfolioID = B.PortfolioID




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.

 
 
meadow0
meadow0
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 48
Thanks for all the help so far.

Initially I thought once I had the query set up, it'd be easy to put a WHERE clause in (for this purpose I'm attempting to limit to [# of Securities] > 70 OR SumOfMV > 100

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
WHERE COUNT(B.SecID) > 70 OR SUM(B.MarketValue) > 100
GROUP BY A.Portfolio, B.PortfolioID

The error is as follows:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.


I would like to learn this stuff, because I am going to eventually be using it everyday. Is there a good place to start?
meadow0
meadow0
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 48
Eh, That was a really easy fix. Sorry for the trivial question. Thanks guys!
calvo
calvo
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1802 Visits: 4007
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.
 

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

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 1721
calvo (3/5/2013)
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.
 

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

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." Crazy

 
meadow0
meadow0
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 48
Can anyone point me to good literature on learning how to do 'Temp Tables' and the like.

Basically I need to create this same exact table with the same exact results, but rather than a query, a temp table.

I can't seem to find any useful information online. Any help is appreciated, thanks!
calvo
calvo
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1802 Visits: 4007
Steven Willis (3/5/2013)
calvo (3/5/2013)
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.
 

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

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." Crazy

 

The only time I've used an OVER() clause is with ROW_NUMBER. I can't wait to give it a shot with aggregates, I was totally unaware that it could be used with them.

Hey, learned something new!

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search