How do I use Max for sum in a SQL Query group by Date

Question

1.00/5 (2 votes)

See more:

Dear Sir,

In a table PaymentDetails there are maney rows in each day for a year. I want to sum total amount date wise and want to get maximum amount(sum of each days) of the year.

Example :

SQL

select max(AmtSUM),sum(TPaidAmt) from Vw_PaymentDetails where FY_Code='1415' group by E_date

TPaidAmt : ColumnName of Amount
AmtSUM : sum(TPaidAmt)

Please rectify the code so that it return the maximum value of the day.

Posted 25-Sep-15 2:15am

LebneizTech

Updated 27-Sep-15 8:28am

Maciej Los

v3

Add a Solution

Comments

CHill60 25-Sep-15 8:30am

So you need to GROUP the data. However it is very difficult to help you without any sample data, expected results and without seeing the code you have already tried

CHill60 25-Sep-15 10:30am

Sample data? Expected results?
Where is AmtSum coming from?

1 solution

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)

Maciej Los · Accepted Answer · 2015-09-27T08:31:00

Solution 1

If you want to get MAX value for single date, try this:

SQL

SELECT E_date, MAX(TPaidAmt)
FROM Vw_PaymentDetails
--WHERE FY_Code='1415'
GROUP BY E_date

But, if you want to get MAX value for SUM(of Amount), try this:

SQL

SELECT E_date, MAX(AmtSUM) AS MaxOfAmtSum
FROM (
     SELECT E_date, UM(TPaidAmt) AS AmtSUM
     FROM Vw_PaymentDetails
     GROUP BY E_date
) AS T
--WHERE FY_Code='1415'

Posted 27-Sep-15 8:31am

Maciej Los

Updated 27-Sep-15 8:43am

v2

Comments

[no name] 27-Sep-15 14:50pm

A 5. BTW. this was a Q for me. You have to concentrate on more difficult Qs with Pivot and similar ;)
Bruno

Maciej Los 27-Sep-15 14:52pm

Thank you, Bruno. I'll promise to respect your suggestion ;)