CODEWITHSUNDEEP
sqlsql-server
sandeep nagabhairava
sandeep nagabhairava

Reputation: 179

Sql query for calculating amount based on shiftno?

My table looks like this:

shiftno     sno     Amount
  100         7       20
              8       50
  101         9       10
             10       30
             11       20

I tried with this query

select SUM(Amount) 
from example 
where shiftNo = (select (max(shiftNo)) from example

It shows result = 10, but actual result is 10 + 30 + 20 = 60. Which was belongs to shiftno=101

How can I get total result of 60?

Upvotes: 1

Views: 68

Answers (1)

juergen d
juergen d

Reputation: 204854

if you want the highest shiftNo use limit 1.
If you want all shiftNo and their amount then leave the limit line:

 select shiftNo, SUM(Amount) as MaxAmount
 from exmple 
 group by shiftNo
 order by shiftNo desc
 limit 1

Upvotes: 4

Related Questions