Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

VB.Net - SQL Queries Not Adding Up

Good Day Expert!

I have finally narrowed down my total counts not matching issue to my queries.  Hopefully you can help.

Select count(*) as 'Count' from [Paidfile] where [Check Date] >= '11/1/2012' and [Check Date] <= '10/31/2013'

Select count(*) as 'EDI Count' from [Paidfile] where [Check Date] >= '11/1/2012' and [Check Date] <= '10/31/2013' and [Batch] = 'XF'      

Select count(*) as 'Paper Count' from [Paidfile] where [Check Date] >= '11/1/2012' and [Check Date] <= '10/31/2013' and [Batch] <> 'XF'  

Ok.  My first query returns 101030.  The second one returns 71729.  The third one returns 29290.  I tracked the 11 count difference to 1 record where [Batch] was blank and 10 records where [Batch] was null.

Shouldn't that 3rd query have caught it when I asked [Batch] <> XF? So, I added [Batch] = '' and it caught the one record where [Batch] = ''.  How do I ask for when [Batch] is Null?

Thanks,
jimbo99999
0
Jimbo99999
Asked:
Jimbo99999
  • 4
  • 2
2 Solutions
 
käµfm³d 👽Commented:
I'm not certain where the difference of 11 that you speak of is coming from. You don't show any numbers that vary by difference of 11. Where is the 11 coming from?

How do I ask for when [Batch] is Null?
Quite literally exactly what you said:

Select count(*) as 'Paper Count' from [Paidfile] where [Check Date] >= '11/1/2012' and [Check Date] <= '10/31/2013' and [Batch] IS NULL

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Shouldn't that 3rd query have caught it when I asked [Batch] <> XF?
Nope nope nope nope nope.

T-SQL can't compare NULL to anything, so <> 'any value' (XF, banana, or even wackadoo) will not pick it up.   In order to get the row count auditing correct you'll have to add this query.

Select count(*) as 'EDI Count' from [Paidfile] 
where [Check Date] IS NULL OR [Batch] IS NULL

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
{Somewhat of a duplicate post, sorry..}
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Jimbo99999Author Commented:
101030 is the TotalCount
71729 is EDICount where [Batch] = 'XF'
29290 is PaperCount where [Batch] <> 'XF

When adding 71729 and 29290, I get 101019.

I don't understand why the totals don't add up.  The query where [Batch] <> 'XF' is not getting the null values.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>The query where [Batch] <> 'XF' is not getting the null values.
Correct.  NULL will NOT (not not not) be captured by <> 'XF', because NULL can't be compared to anything, meaning it will return a NULL back.  You'll need to use ISNULL() or COALESCE() to handle the NULLS.

Such as..
Select count(*) as 'Paper Count' from [Paidfile] 
where [Check Date] >= '11/1/2012' and [Check Date] <= '10/31/2013' and ISNULL([Batch], 'wahooooo') <> 'XF'

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
To illustrate, copy-paste the below code into your SSMS, and let 'er rip
IF OBJECT_ID('tempdb..#foo') IS NOT NULL
   DROP TABLE #foo
GO

CREATE TABLE #foo (num int)

INSERT INTO #foo (num) 
VALUES 
   (1), (1), (1), (1), (1), 
   (14), (42), (99), (90210), (812), 
   (NULL), (NULL)
	
SELECT 'Row count, including NULL', COUNT(*) FROM #foo
SELECT 'Row count, not including NULL', COUNT(num) FROM #foo
SELECT 'The 1s', COUNT(*) FROM #foo WHERE num = 1
SELECT 'The not 1s, not including NULLs', COUNT(*) FROM #foo WHERE num <> 1
SELECT 'The not 1s, including NULL', COUNT(*) FROM #foo WHERE ISNULL(num, -1) <> 1
SELECT 'The NULLs', COUNT(*) FROM #foo WHERE num IS NULL

Open in new window

0
 
Jimbo99999Author Commented:
Thanks very much for both of your responses.  It is working now!

I always enjoying learning new things everyday when it comes to programming.

Thanks again,
jimbo99999

jim: I will try your example here this afternoon...I have to get the report formatted and to the requestor.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now