Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Really confused newbie - Perhaps a Temp Table query? Expand / Collapse
Author
Message
Posted Tuesday, March 5, 2013 8:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:06 AM
Points: 21, 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!

Post #1426879
Posted Tuesday, March 5, 2013 9:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:28 PM
Points: 2,386, Visits: 7,611
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;




Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1426890
Posted Tuesday, March 5, 2013 9:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:06 AM
Points: 21, 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
Post #1426901
Posted Tuesday, March 5, 2013 9:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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.

 
 
Post #1426902
Posted Tuesday, March 5, 2013 10:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:06 AM
Points: 21, 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?










Post #1426912
Posted Tuesday, March 5, 2013 10:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:06 AM
Points: 21, Visits: 48
Eh, That was a really easy fix. Sorry for the trivial question. Thanks guys!
Post #1426916
Posted Tuesday, March 5, 2013 10:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 24, 2014 11:10 AM
Points: 1,269, Visits: 3,631
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.
Post #1426919
Posted Tuesday, March 5, 2013 11:26 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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."

 
Post #1426957
Posted Tuesday, March 5, 2013 11:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:06 AM
Points: 21, 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!
Post #1426959
Posted Tuesday, March 5, 2013 11:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 24, 2014 11:10 AM
Points: 1,269, Visits: 3,631
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."

 

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.
Post #1426962
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse