Link to home
Start Free TrialLog in
Avatar of jdr0606
jdr0606Flag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of jonnidip
jonnidip
Flag of Italy image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Avatar of jdr0606

ASKER

Cleanup was the solution!

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

Thanks