SQL MAX and SUM FUNCTION

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

Hi there hope you can help me!

I have the following table named StockTable

StockName|StockPrice | StockDate

DOW 23 01/01/10
RSDB 15 01/01/10
DOW 22 02/01/10
RSDB 11 02/01/10
DOW 24 03/01/10
RSDB 12 03/01/10
DOW 25 04/01/10
RSDB 16 04/01/10

I need to get the latest row by stockDate for each StockName which i have achieved with the following SQL:

SELECT StockName, StockPrice, StockDate
FROM StockTable
WHERE (StockDate = (SELECT MAX(StockDate)
FROM StockTable))

Which returns this table as the most recent date is 04/01/10

StockName|StockPrice | StockDate

DOW 25 04/01/10
RSDB 16 04/01/10

This next bit is the part i am struggling with

i need to get the sum of the StockPrice within the same sql statment to return

|StockPrice|

41

Hope you can help me and if you need any clarification then please ask!

Thanks

Posted 17-Jan-10 4:43am

necrokill1985

Add a Solution

2 solutions

Solution 2

THanks that works great!!!!

you are a life saver!!!

Posted 17-Jan-10 5:24am

necrokill1985

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2010-01-17T04:57:00

Your current query:

SELECT StockName, StockPrice, StockDate
FROM StockTable
WHERE (StockDate = (SELECT MAX(StockDate)
FROM StockTable))

New query:

SELECT SUM(StockPrice) as StockPriceSum
FROM StockTable
WHERE (StockDate = (SELECT MAX(StockDate)
FROM StockTable))

Result of it would be the SUM. E.g. 41 here.