SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query not pulling correct Count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ncurran217
Starting Member

23 Posts

Posted - 08/16/2013 :  14:21:13  Show Profile  Reply with Quote
SELECT        Records.JulianDate, Records.Records, COUNT(sales_view.JulianDate) AS Sales, COUNT(cancels_view.JulianDate) AS Cancels
FROM            Records LEFT OUTER JOIN
                         cancels_view ON Records.Listcode = cancels_view.listcode AND Records.JulianDate = cancels_view.JulianDate LEFT OUTER JOIN
                         sales_view ON Records.Listcode = sales_view.listcode AND Records.JulianDate = sales_view.JulianDate
GROUP BY Records.JulianDate, Records.Records, Records.Listcode
HAVING        (Records.Listcode = 'LM') AND (Records.JulianDate = '347')
ORDER BY LEN(Records.JulianDate) DESC, Records.JulianDate DESC


That is my code and it returns this:

Julian Date Records Sales Cancels
347 15000 2 2

The problem is there is only 1 record in the cancels table that has a listcode of LM and JulianDate of 347. And if I run a count separate on the cancels table it shows correctly of 1, but when I run it like this with three tables joined together it counts the same amount as the sales. There are two records in the sales table that has listcode of LM and JulianDate of 347, and the records is correct as well. Just the cancels number is wrong. What am I doing incorrectly? Thanks for the help in advance!

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/16/2013 :  14:27:44  Show Profile  Reply with Quote
replace
COUNT(cancels_view.JulianDate)

with

COUNT(DISTINCT cancels_view.UniqueIDField) AS Cancels



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ncurran217
Starting Member

23 Posts

Posted - 08/16/2013 :  14:35:42  Show Profile  Reply with Quote
Thank you very much, that worked perfectly!
Go to Top of Page

ncurran217
Starting Member

23 Posts

Posted - 08/16/2013 :  16:23:46  Show Profile  Reply with Quote
Actually, now that I have put big amounts of data into it, every record that has a cancel only shows 1, and not the correct amount. Also, when the sales are off now as well. When I look directly at the sales and do

WHERE listcode = 'AM and JulianDate = '362'


I get 17 sales and when I add those same WHERE in the cancels_view it has 7 records. But when I do the full long query, it has 119 sales and 1 cancel. Why is this?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/18/2013 :  04:30:08  Show Profile  Reply with Quote
quote:
Originally posted by ncurran217

Actually, now that I have put big amounts of data into it, every record that has a cancel only shows 1, and not the correct amount. Also, when the sales are off now as well. When I look directly at the sales and do

WHERE listcode = 'AM and JulianDate = '362'


I get 17 sales and when I add those same WHERE in the cancels_view it has 7 records. But when I do the full long query, it has 119 sales and 1 cancel. Why is this?


sorry cant understand your scenario from explanation
Please post some sample data and explain what you get and what you actually want.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000