sql quary for calculate sum?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

hi experts,

my table was like this, table name exmple.

SQL

shiftno     sno     Amount

  100         7       20
              8       50
  101         9       10
              10      30
              11      20

i tried with this quary
select SUM(Amount) from exmple where shiftNo=(select (max(shiftNo)) from exmple
it shoes reselt=10,but auctual result is 10+30+20=60. which was belongs to shift no 101
how can i get total result of 60?

could you please advice...
thanks in advance...

Posted 8-Jun-12 2:06am

sandeep nagabhairava

Updated 8-Jun-12 2:41am

v3

Add a Solution

Comments

Manfred Rudolf Bihy 8-Jun-12 8:18am

Unclear! What do you mean by "where max(shiftno)"? Please explain in more detail and try to give an example of what the expected results would be.

Thanks for your cooperation!

sandeep nagabhairava 8-Jun-12 8:51am

now i improved my question... if you are free,please see once.
thanks.

Sandeep Mewara 8-Jun-12 12:38pm

I already replied the reason to you in my answers & comments.

sandeep nagabhairava 8-Jun-12 13:20pm

ok... thanks alot.

6 solutions

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)

Sridhar Patnayak · Answer 1 · 2012-06-08T02:17:00

Solution 1

Question is not clear, as per my understanding
you want to get the sum of amount for max shiftno

select sum(amount) from <your table name> where shiftno=(select max(shiftno) from <your table name>

This will help you

Thanks,
SP

Posted 8-Jun-12 2:17am

Sridhar Patnayak

Updated 8-Jun-12 2:23am

v2

Comments

sandeep nagabhairava 8-Jun-12 8:22am

i tried in this way,but i get answer 10

Sridhar Patnayak 8-Jun-12 8:25am

Give some more details

Manas Bhardwaj 8-Jun-12 8:28am

Just a question. Is ShiftNo only available in 1st and 3rd row?

sandeep nagabhairava 8-Jun-12 8:44am

yes,amount of 10,30,20 belongs to shiftno 101. with ur quary iam getting ans 10 only, auctual ans 60.
thanks

Sandeep Mewara 8-Jun-12 9:34am

Replied. See my comment.

Manas Bhardwaj 8-Jun-12 8:26am

correct +5

Sridhar Patnayak 8-Jun-12 8:28am

Thanks Bhardwaj

Maciej Los 18-Apr-13 7:18am

+5

Manas Bhardwaj · Answer 2 · 2012-06-08T02:21:00

Solution 2

SQL

SELECT SUM(AMOUNT) FROM MyTable WHERE ShiftNo = (SELECT MAX(ShiftNo) FROM MyTable)

Posted 8-Jun-12 2:21am

Manas Bhardwaj

Comments

Sandeep Mewara 8-Jun-12 8:22am

Ditto! 5!

Manas Bhardwaj 8-Jun-12 8:25am

That's scary. Can you read mind as well? (for MyTable) :)

Sandeep Mewara 8-Jun-12 8:27am

That's my convention in general. :)

Maciej Los 8-Jun-12 18:31pm

Good work! +5

Sandeep Mewara · Answer 3 · 2012-06-08T02:21:00

Solution 3

Try:

SQL

SELECT SUM(Amount) as AmountSum
FROM MyTable
WHERE (ShiftNo = (SELECT MAX(ShiftNo)FROM MyTable))

Posted 8-Jun-12 2:21am

Sandeep Mewara

Updated 8-Jun-12 2:22am

v2

Comments

Manas Bhardwaj 8-Jun-12 8:26am

Correct +5

Sandeep Mewara 8-Jun-12 8:27am

Thanks.

Sandeep Mewara 8-Jun-12 9:33am

Fill shiftno for rest of the two rows too!
An empty cell of shiftno for the rows with 30 & 20 amount is not getting picked currently.

Maciej Los 8-Jun-12 18:31pm

3 x yes!

Maciej Los 8-Jun-12 18:31pm

Good work, 5!

Prasad_Kulkarni · Answer 4 · 2012-06-08T02:23:00

Solution 4

SQL

select SUM(amount) from tableName 
where ShiftNo = (select MAX(ShiftNo) from tableName)

Posted 8-Jun-12 2:23am

Prasad_Kulkarni

Comments

Manas Bhardwaj 8-Jun-12 8:26am

correct +5

Prasad_Kulkarni 8-Jun-12 8:40am

:) thank you, but where's 5!

Manas Bhardwaj 8-Jun-12 9:14am

Oops!!! Same happened with VJ this morning.
Now you have it. :)

Maciej Los 8-Jun-12 18:32pm

Good work, my 5!

Prasad_Kulkarni 10-Jun-12 23:52pm

Thank you Isomac!

pvchandana · Answer 5 · 2012-06-08T06:24:00

Solution 5

select SUM(Amount) from exmple where shiftNo IN (select (max(shiftNo)) from exmple )

Posted 8-Jun-12 6:24am

pvchandana

Comments

sandeep nagabhairava 8-Jun-12 13:21pm

thanks chandana

Maciej Los · Answer 6 · 2012-06-08T12:41:00

Another solution:

SQL

DECLARE @shiftno INT

SELECT @shiftno=MAX([shiftno])
FROM Table1

SELECT SUM([amount]) AS SumOfAmount
FROM Table1
WHERE [shiftno]=@shiftno

It should works too:

SQL

SELECT SUM([amount]) AS SumOfAmount
FROM Table1
HAVING 1=MAX([shiftno])

To get the sum of amount for each shiftno:

SQL

SELECT [shiftno], SUM([amount]) AS SumOfAmount
FROM Table1
GROUP BY [shiftno]

sql quary for calculate sum?

6 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Solution 6

Add your solution here

Preview 0

Existing Members

...or Join us