jdr0606
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 .Multiplie rValue),2) as 'MaxUPrice'
, round(min(sop2.UNITPRCE/mv .Multiplie rValue),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.itemn mbr)) as 'Counted'
from kg.dbo.sop10200_sop30300_u nion sop2
join kg.dbo.sop10100_sop30200_u nion sop1 on sop1.sopnumbe=sop2.sopnumb e
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.udtMultip lierValues mv on mv.multiplier=rm1.prclevel
left join dynamics_ext.dbo.udtNonInv entoryLog udtNon on udtNon.NonInventoryID=sop2 .itemnmbr
left join kg.dbo.eiv00800 eiv8 on eiv8.custnmbr=sop1.custnmb r
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.Mu ltiplierVa lue,2)
order by sop2.itemnmbr
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
, round(min(sop2.UNITPRCE/mv
, count(sop2.itemnmbr) as 'UnitCount'
, year(sop1.docdate) as 'DocYear'
, Month(sop1.docdate) as 'DocMonth'
, max(ltrim(rm1.prclevel)) as 'Multiplier'
,count(distinct(sop2.itemn
from kg.dbo.sop10200_sop30300_u
join kg.dbo.sop10100_sop30200_u
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.udtMultip
left join dynamics_ext.dbo.udtNonInv
left join kg.dbo.eiv00800 eiv8 on eiv8.custnmbr=sop1.custnmb
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.Mu
order by sop2.itemnmbr
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cleanup was the solution!
That's what happens when someone copies and pastes from another query.
Thanks
That's what happens when someone copies and pastes from another query.
Thanks
Try the following query where I took out the tables that don't seem to be needed in your results.
What are the results
Open in new window