[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Count in SQL Select

Posted on 2012-09-20
3
Medium Priority
?
698 Views
Last Modified: 2012-09-21
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
0
Comment
Question by:jdr0606
3 Comments
 
LVL 13

Accepted Solution

by:
jonnidip earned 2000 total points
ID: 38420650
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
 
LVL 6

Expert Comment

by:NikolasG
ID: 38420736
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
 

Author Closing Comment

by:jdr0606
ID: 38421660
Cleanup was the solution!

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

Thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question