I have a select statement using group by andincludes a count in the statement but it is giving me incorrect count results.
I'm searching a table for a particular part (#2001) that was sold in 2010 and the month of September. There were a total of 15 times that this part was sold but I'm getting the following results for count using the below query. What is wrong with my select syntax.
The first row has a count of 6032
the second 3
the third 1863
the fourth 1
#2001 138.9100000000000000 138.9100000000000000 6032 2010 9 JOHN 1
#2001 141.1100000000000000 141.1100000000000000 3 2010 9 460 1
#2001 141.1200000000000000 141.1200000000000000 1863 2010 9 430 1
#2001 147.5200000000000000 147.5200000000000000 1 2010 9 440 1
select sop2.itemnmbr as 'ItemNumber'
, round(max(sop2.UNITPRCE/mv.MultiplierValue),2) as 'MaxUPrice'
, round(min(sop2.UNITPRCE/mv.MultiplierValue),2) as 'MinUPrice'
, count(sop2.itemnmbr) as 'UnitCount'
, year(sop1.docdate) as 'DocYear'
, Month(sop1.docdate) as 'DocMonth'
, max(ltrim(rm1.prclevel)) as 'Multiplier'
,count(distinct(sop2.itemnmbr)) as 'Counted'
from kg.dbo.sop10200_sop30300_union sop2
join kg.dbo.sop10100_sop30200_union sop1 on sop1.sopnumbe=sop2.sopnumbe
left join kg.dbo.iv00101 iv1 on iv1.itemnmbr=sop2.itemnmbr
left join kg.dbo.rm00101 rm1 on rm1.custnmbr=sop1.custnmbr
left join dynamics_ext.dbo.udtMultiplierValues mv on mv.multiplier=rm1.prclevel
left join dynamics_ext.dbo.udtNonInventoryLog udtNon on udtNon.NonInventoryID=sop2.itemnmbr
left join kg.dbo.eiv00800 eiv8 on eiv8.custnmbr=sop1.custnmbr
and (sop1.docdate < eiv8.expndate
and rm1.prclevel<>'LE SING')
and (sop1.docdate >'2010-09-01'
and sop1.docdate <'2010-09-30')
group by sop2.itemnmbr
order by sop2.itemnmbr