SQL Server Query some specific condition

问题:

SELECT distinct t1.vssyspackageid,CA.rAmount as Amount,Curr.vsShortCode AS Currency       


 from tblPrograms         
   INNER JOIN tblProgramsAndPackages ON tblPrograms.vsSysProgramId = tblProgramsAndPackages.vsSysProgramId         
   inner join tblPackages t1 on tblProgramsAndPackages.iPackageId=t1.iPackageId        
  right join tblPkgContractAwardDetails as CA on CA.iPackageId=t1.iPackageId  
   join tblCurrencies as Curr on CA.iCurrency =Curr.iCurrencyId    


   where tblPrograms.vsSysProgramId='JICA'        
  group by t1.vssyspackageid,CA.rAmount,Curr.vsShortCode

if which package assigned to Contractor Award Detail then it will show in one column.

Example: GWSSP/01,GWSSP/02 then after it it shows total package in next column.

回答1:

I'm not sure exactly what you are wanting as your question is a little vague, but I'm guessing that you want a single row for the specified Package that will show the sum of the Amount column. This is what I can understand from your question anyway!

SELECT DISTINCT t1.vssyspackageid, 
                SUM(CA.rAmount) AS TotalAmount,
                Curr.vsShortCode AS Currency       
FROM            tblPrograms         
INNER JOIN      tblProgramsAndPackages ON tblPrograms.vsSysProgramId = 
                                          tblProgramsAndPackages.vsSysProgramId         
INNER JOIN      tblPackages AS t1 ON tblProgramsAndPackages.iPackageId = 
                                     t1.iPackageId        
RIGHT JOIN      tblPkgContractAwardDetails AS CA ON CA.iPackageId = 
                                                    t1.iPackageId  
JOIN            tblCurrencies AS Curr ON CA.iCurrency = 
                                         Curr.iCurrencyId    
WHERE           tblPrograms.vsSysProgramId = 'JICA' 
     AND        t1.[YOUR_COLUMN_NAME_HERE] = 'GWSSP/01'
GROUP BY        t1.vssyspackageid,
                Curr.vsShortCode

Just a few things to note:

1. I don't know the column name in the tblPackages table that the values such as 'GWSSP/01' belongs, so I have left that for you to fill in.
2. You will still get multiple rows if there are multiple currencies for your package, you will have to remove the Curr.vsShortCode from your Select and Group By clauses if that is the case.
3. I also tided up your code a bit because it was quite messy and hard to understand as well as using multiple standards and short-cuts!