troubleshooting Question

Getting a wrong count in query

Avatar of sqlcurious
sqlcuriousFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
3 Comments1 Solution353 ViewsLast Modified:
select  distinct il.Sale_No, customer_no,count(Il.Invoice_No) NoofInv from  h.dbo.inv_lots il
       INNER JOIN i.dbo.invoice i
         ON il.sale_no = i.sale_no
            AND i.invoice_no = il.invoice_no where Customer_No = 212736
             and il.sale_no IN( 121, 121208, 3516 )
             GROUP BY  il.Sale_No, customer_no

For the above query I get the following result:
Sale_No      customer_no      NoofInv
3516      212736                        358

But the NoofInv - 358 is a wronf answer, becuase when I try this query:
select  distinct il.Sale_No, customer_no,(Il.Invoice_No) NoofInv from  hnai.dbo.inv_lots il
       INNER JOIN hnai.dbo.invoice i
         ON il.sale_no = i.sale_no
            AND i.invoice_no = il.invoice_no where Customer_No = 212736
             and il.sale_no IN( 121, 121208, 3516 )
I get the following result, which tells me there are only 3 invoices, not sure where I am going wrong:
Sale_No      customer_no      NoofInv
3516      212736      10279910
3516      212736      10282521
3516      212736      10282876
ASKER CERTIFIED SOLUTION
Michael Carrillo
Information Systems Manager
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros