Solved

Getting a wrong count in query

Posted on 2012-03-13
3
320 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:sqlcurious
3 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 37717550
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

0
 
LVL 9

Accepted Solution

by:
macarrillo1 earned 500 total points
ID: 37724923
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.
0
 

Author Closing Comment

by:sqlcurious
ID: 37778414
thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now