Count in SQL Select

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 eiv8.multiplierval>0)
where sop1.soptype='2'
      and (rm1.prclevel<>'LE'
            and rm1.prclevel<>'LE SING')
      and (sop1.docdate >'2010-09-01'
            and sop1.docdate <'2010-09-30')
      and sop2.itemnmbr='#2001'      
group by sop2.itemnmbr
      ,year(sop1.docdate)
      ,Month(sop1.docdate)
      ,round(sop2.UNITPRCE/mv.MultiplierValue,2)
order by sop2.itemnmbr
jdr0606Asked:
Who is Participating?
 
jonnidipConnect With a Mentor Commented:
I suggest you to clean up your query.
There are a lot of "join" that seem to be useless.
In your query you take values only from "sop2", "sop1" and "rm1".
I would remove all other tables from your join.
This could be the problem, since if not all the tables have the same key, you can get multiple values (rows) from them. Then the result is grouped, so you don't feel that being a problem...

Regards.
0
 
NikolasGCommented:
I agree with Jonnidip.

Try the following query where I took out the tables that don't seem to be needed in your results.
What are the results
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.rm00101 rm1 on rm1.custnmbr=sop1.custnmbr
where sop1.soptype='2'
      and (rm1.prclevel<>'LE'
            and rm1.prclevel<>'LE SING')
      and (sop1.docdate >'2010-09-01'
            and sop1.docdate <'2010-09-30')
      and sop2.itemnmbr='#2001'      
group by sop2.itemnmbr
      ,year(sop1.docdate)
      ,Month(sop1.docdate)
      ,round(sop2.UNITPRCE/mv.MultiplierValue,2)
order by sop2.itemnmbr 

Open in new window

0
 
jdr0606Author Commented:
Cleanup was the solution!

That's what happens when someone copies and pastes from another query.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.