distinct and sum in single join query-CodePudding

I have the following join query.. I want records to be distinct with c.checkid and also sum of earnings.. how can I perform both in one single query?

select c.CheckID,cv.CheckID,Earnings,TaxableEarnings,Amount,EmployeeCode, CheckDate FROM [Customers0].[pay].[CheckVendorCalc] as cv
inner join  [Customers0].[pay].[Checks] as c
on cv.checkid=c.checkid
where
c.CheckDate BETWEEN '2022-01-01' AND '2022-12-31' and 
c.CustomerID=360 and
c.EmployeeCode='01' and
(cv.TaxableEarnings !=null or cv.TaxableEarnings!=0)

CodePudding user response：

You can use GROUP BY. Any non-aggregated columns in the SELECT clause must be present in the GROUP BY clause. You can find more information here.

SQL:

select
  c.CheckID,
  SUM(Earnings) as totalearning
FROM
  [Customers0].[pay].[CheckVendorCalc] as cv
  inner join [Customers0].[pay].[Checks] as c on cv.checkid = c.checkid
where
  c.CheckDate BETWEEN '2022-01-01'
  AND '2022-12-31'
  and c.CustomerID = 360
  and c.EmployeeCode = '01'
  and (
    cv.TaxableEarnings != null
    or cv.TaxableEarnings != 0)
group by
  c.CheckID
order by
  c.CheckID