Query not pulling correct Count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Query not pulling correct Count

Author

Topic

ncurran217
Starting Member

23 Posts

Posted - 2013-08-16 : 14:21:13

[code]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[/code]

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

52326 Posts

Posted - 2013-08-16 : 14:27:44

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

ncurran217
Starting Member

23 Posts

Posted - 2013-08-16 : 14:35:42

Thank you very much, that worked perfectly!

ncurran217
Starting Member

23 Posts

Posted - 2013-08-16 : 16:23:46

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-18 : 04:30:08

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources