CODEWITHSUNDEEP
mysqlsqlsql-server
Sales Mlmportal
Sales Mlmportal

Reputation: 3

Sql select top query

hello guys I have the following query:

   SELECT TOP(1) CommitmentLog.CommitAmt
FROM   leveldetails
       INNER JOIN CommitmentLog
               ON leveldetails.App_ID = CommitmentLog.app_id
                  AND leveldetails.Intro_ID = '999'
                  AND leveldetails.Side = 'right'
                  AND CommitmentLog.flag != '1'
                  AND CommitmentLog.CommitNo NOT IN (SELECT commit_no
                                                     FROM   binary_bal
                                                     WHERE  Intro_ID = '999'
                                                            AND Side = 'right'
                                                            AND llevel BETWEEN 1 AND 100000)

1st row CommitAmt value : 1500
2nd row CommitAmt value: 500
If i select the top 1 sum of CommitmentLog.CommitAmt the return value is 2000 rather than 1500
If i select the top 2 sum of CommitmentLog.CommitAmt the return value 2000 which is correct

if i select top 1 then sum(CommitmentLog.CommitAmt) it should return 1500 and
if i select top 2 then sum(CommitmentLog.CommitAmt) it should return 1500 + 500 = 2000

Upvotes: 0

Views: 345

Answers (1)

TheProvost
TheProvost

Reputation: 1893

You want to Sum top N of your result set? Try this

SELECT SUM(t1.CommitAmt) from (SELECT TOP(1) CommitmentLog.CommitAmt
                                  FROM   leveldetails
                                    INNER JOIN CommitmentLog
                                      ON leveldetails.App_ID = CommitmentLog.app_id
                                      AND leveldetails.Intro_ID = '999'
                                      AND leveldetails.Side = 'right'
                                      AND CommitmentLog.flag != '1'
                                      AND CommitmentLog.CommitNo NOT IN (SELECT commit_no
                                                     FROM   binary_bal
                                                     WHERE  Intro_ID = '999'
                                                            AND Side = 'right'
                                                            AND llevel BETWEEN 1 AND 100000)
                              ) AS t1

Upvotes: 1

Related Questions