select count(*)from(SELECT
A.MATNR ARTICLE,
A.WERKS SITE,
ROUND((SUM(COALESCE((CASEWHEN A.BWART IN('551','951')THEN(A.DMBTR)END),0))-SUM(COALESCE((CASEWHEN A.BWART IN('552','952')THEN(A.DMBTR)END),0)))) DUMP_VALUE,
ROUND((SUM(COALESCE((CASEWHEN A.BWART IN('551','951')THEN(A.MENGE)END),0))-SUM(COALESCE((CASEWHEN A.BWART IN('552','952')THEN(A.MENGE)END),0)))) DUMP_QTY
FROM MSEG A
INNERJOIN S_SITE_MASTER B ON A.WERKS=B.STORE_NO
INNERJOIN S_PRODUCT_MASTER C ON A.MATNR=C.MATERIAL
WHERE A.BUDAT_MKPF>=ADD_MONTHS(NEXT_DAY(LAST_DAY(ADD_DAYS(CURRENT_DATE,-1))),-1)AND A.BUDAT_MKPF<=ADD_DAYS(CURRENT_DATE,-1)AND A.BWART IN('551','552','951','952')AND C.LEVEL2=10AND B.FORMAT_CD IN('1')GROUPBY A.MATNR,A.WERKS);
Output count is - 129790
IF i just change clause AND B.FORMAT_CD IN ('1') to AND B.FORMAT_CD IN (1), like below -
select count(*) from(
SELECT
A.MATNR ARTICLE,
A.WERKS SITE,
ROUND((SUM(COALESCE((CASE WHEN A.BWART IN('551','951') THEN(A.DMBTR) END),0))-SUM(COALESCE((CASE WHEN A.BWART IN('552','952') THEN(A.DMBTR) END),0)))) DUMP_VALUE,
ROUND((SUM(COALESCE((CASE WHEN A.BWART IN('551','951') THEN(A.MENGE) END),0))-SUM(COALESCE((CASE WHEN A.BWART IN('552','952') THEN(A.MENGE) END),0)))) DUMP_QTY
FROM MSEG A
INNER JOIN S_SITE_MASTER B ON A.WERKS=B.STORE_NO
INNER JOIN S_PRODUCT_MASTER C ON A.MATNR=C.MATERIAL
WHERE A.BUDAT_MKPF>=ADD_MONTHS(NEXT_DAY(LAST_DAY(ADD_DAYS(CURRENT_DATE,-1))),-1)
AND A.BUDAT_MKPF<=ADD_DAYS(CURRENT_DATE,-1)
AND A.BWART IN ('551','552','951','952')
AND C.LEVEL2=10
AND B.FORMAT_CD IN (1)
GROUP BY A.MATNR,A.WERKS);
Ouput count - 29403 (which is correct count)
Datatype of B.FORMAT_CD is NVARCHAR(3).
How does count behave differently?