Getting a wrong count in query

sqlcurious
sqlcurious used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
why the distinct?
A group by is already the distinct. And in your other query it could be that the distinct hides rows.

Guess you want to count distinct  Il.Invoice_No's

select  il.Sale_No, customer_no, count(distinct  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

Open in new window

Information Systems Manager
Top Expert 2012
Commented:
Your first query is using Il.Invoice_No in a calculation of count.  So distinct is not applied to this column, it is counting all Il.Invoice_No.

Your second query is applying distinct to the column Il.Invoice_No and so it returns fewer rows.

If you add this column (Il.Invoice_No) to the first query you should get the same result as the bottom query.

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial