Aggregate functions - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Aggregate functions

Author

Topic

jcarrallo
Starting Member

35 Posts

Posted - 2014-06-13 : 14:11:15

Hi Guys,

Thanks for your great work.

I have a simple query like:

select max(PTR_DATE)
from MPR_portfolio_transactions
group by PTR_SYMBOL

and this is working fine, but if I add an extra column with another field like:

select max(PTR_DATE) , PTR_SHAREBALANCE
from MPR_portfolio_transactions
group by PTR_SYMBOL

Then I get an error message like:

Column 'MPR_portfolio_transactions.PTR_SHAREBALANCE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I get round to this?
Please help!
Jay

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-13 : 14:15:34

What do you want to do with the PTR_ShareBalance column?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

jcarrallo
Starting Member

35 Posts

Posted - 2014-06-13 : 14:21:36

Just to show the resulting number. Let me give you and example:

Lets say I have 2 records:

symbol= GOOG
PTR_DATE= 01/01/2014
PTR_SHAREBALANCE= 1

Symbol= GOOG
PTR_DATE= 02/01/2014
PTR_SHAREBALANCE= 2

The max(PTR_DATE) function will give me the result of 02/01/2014. Next to it I want to show PTR_BALANCE for that record ie: 2

Sio the result of the query would be;

02/01/2014 | 2

thank you

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-13 : 14:26:35

You can use the ROW_NUMBER function like shown below:

;WITH cte AS
(
	SELECT 
		Symbol,
		PTR_DATE,
		PTR_SHAREBALANCE,
		ROW_NUMBER() OVER(PARTITION BY Symbol ORDER BY PTR_DATE DESC) AS RN -- construct a row number
	FROM
		YourTable
)
SELECT Symbol, PTR_DATE,PTR_SHAREBALANCE 
FROM cte
WHERE RN = 1;

You could also use a join or a cross apply, but this is likely to be more efficient than those methods.

jcarrallo
Starting Member

35 Posts

Posted - 2014-06-13 : 14:29:45

really cool!!!
many thanks

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-14 : 01:20:12

We can do it in a single line ..Try learning about USING PARTITION CLAUSE WITH AGGREGATE FUNCTIONS


SELECT  MAX(PTR_DATE) OVER (PARTITION BY  PTR_SYMBOL) AS PTR_DATE
      , PTR_SHAREBALANCE
FROM    MPR_portfolio_transactions

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-14 : 04:48:54

Why would you do that? In your code you will show the same date for all balances.
Kind of weird, if you ask me. Not to mention you are not getting the result original poster asked for.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources