asked on

Getting a wrong count in query

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

jogos

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

ASKER CERTIFIED SOLUTION

Michael Carrillo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sqlcurious

ASKER

thanks