Kazuya Marino Kazuya Marino - 3 months ago 16
SQL Question

SQL Sum Column Between Two Dates

I have problem to sum price column between two dates in SQL Server.

I have query like this :

SELECT
OSLP.SlpName as Salesman,
CAST(OINV.DocTotal as float) as Achiev,
OINV.TaxDate
FROM
OINV
INNER JOIN
INV1 ON INV1.DocEntry = OINV.DocEntry
INNER JOIN
OSLP ON OINV.SlpCode = OSLP.SlpCode
INNER JOIN
OITM ON INV1.ItemCode = OITM.ItemCode
INNER JOIN
OMRC ON OITM.FirmCode = OMRC.FirmCode
INNER JOIN
OCRD ON OINV.CardCode = OCRD.CardCode
WHERE
OSLP.SlpName LIKE '01-2 Ika'
AND OINV.TaxDate BETWEEN '20160804' AND '20160806'
GROUP BY
OSLP.SlpName, OINV.DocTotal, OINV.TaxDate


The result of the query above is like this :

enter image description here

I have also added SUM on "CAST(OINV.DocTotal as float) as Achiev" like this :

CAST(sum(OINV.DocTotal) as float) as Achiev


But, it returns wrong result :

enter image description here

The correct result of the Achiev column on the dates should be
4906230


Many thanks for the help!

UPDATE (SOLVED)
I have solved the problem. It just add distinct to the SUM query, because there is a duplicate of data in my query.

Query :

SELECT
OSLP.SlpName as Salesman,
sum(CAST(INV1.Quantity as float)) as Qty,
sum(DISTINCT CAST(OINV.DocTotal as float)) as Achiev
FROM
OINV
INNER JOIN
INV1 ON INV1.DocEntry = OINV.DocEntry
INNER JOIN
OSLP ON OINV.SlpCode = OSLP.SlpCode
INNER JOIN
OITM ON INV1.ItemCode = OITM.ItemCode
INNER JOIN
OMRC ON OITM.FirmCode = OMRC.FirmCode
INNER JOIN
OCRD ON OINV.CardCode = OCRD.CardCode
WHERE
OSLP.SlpName LIKE '01-2 Ika'
AND OINV.TaxDate BETWEEN '20160804' AND '20160806'
GROUP BY
OSLP.SlpName


it returns :
enter image description here

Thanks to everyone who has helped me!

Answer

Issue is with group by .. remove OINV.DocTotal from group by and do sum like below

SELECT
OSLP.SlpName as Salesman,
sum(CAST(OINV.DocTotal as float)) as Achiev,
OINV.TaxDate
FROM OINV
  INNER JOIN INV1 ON INV1.DocEntry = OINV.DocEntry
  INNER JOIN OSLP ON OINV.SlpCode = OSLP.SlpCode
  INNER JOIN OITM ON INV1.ItemCode = OITM.ItemCode
  INNER JOIN OMRC ON OITM.FirmCode = OMRC.FirmCode
  INNER JOIN OCRD ON OINV.CardCode = OCRD.CardCode
WHERE
OSLP.SlpName like '01-2 Ika' And OINV.TaxDate between '20160804' and '20160806'
GROUP BY
  OSLP.SlpName,
  OINV.TaxDate