MS SQL returns 1 row on no match found with null values

I have the following query, which returns 1 row with null segpax, because there is no matching data.

SELECT  sum(segpax) segpax
FROM jismit_TCNdetail
WHERE
(issdate between '20080801' AND '20080831') AND
(fltdate between '20080801' AND '20090831') AND
(oddst = 'xxx') AND
(dmtseg = 'Y') ;

However this returns 0 row, which is correct (no sum);

SELECT  segpax
FROM jismit_TCNdetail
WHERE
(issdate between '20080801' AND '20080831') AND
(fltdate between '20080801' AND '20090831') AND
(oddst = 'xxx') AND
(dmtseg = 'Y') ;

It seems if I am using sum I need group by, in which case I get 0 rows as well.  If I have no values to group by and criteria matches, all is welll.  Criteria does not match and no group by I get 1 null row....

Thanks
CPOINT2000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
nice, but what is your question?
0
sammySeltzerCommented:
You really don't have to use group by based on what you showed above but you can if you want to and I don't think that's the reason you get null values.

My question is did you check to ensure there are actually values associated with segpax?

Also, can you post just one value of issdate?

I am assuming issdate is of datetime datatype no?
0
sammySeltzerCommented:
Nice catch there HainKurt!
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

HainKurtSr. System AnalystCommented:
put a group by

group by (case id when null then 0 else 1 end)

where id is a non null primary key of the table, it has no effect on result, just to make group by works
0
CPOINT2000Author Commented:
HainKurt, question is based on what is happening between when there is matching data in DB and when there isn't why do I get what I get.  As I explained if I have matching data in DB, 1 row is returned as it should.  If there is no data found for the query in DB, 1 row returned with NULL under SEGPAX column.  It did not find anything, yet result query has 1 row with values NULL.  However if I display one of the fields and add it to GROUP BY line, then it will come back with 0 rows returned, as it should.  Not sure how else I can explain.  

I can force a group by if I have to, but I am trying to understand why the 1 row is returned with NULL when there is no match.

Thanks
0
CPOINT2000Author Commented:
Sorry guys, question was assumed :)
0
CPOINT2000Author Commented:
HainKurt, putting a group by resolves it, I know that.  I was trying to understand is Group By required?  In grand scheme of things, user will select various display items, based on this summmation fields will calculate and my logic will put Group By.  So normally this works.  I came accross this problem when my Tester happen to run a query without any display items (non computed fields), which I did not take into consideration at design time and did not build Group By.  Maybe I need to, or force them to select at least one non-computed field...
0
CPOINT2000Author Commented:
Sammy, yes data is all there, there is no issue with the data in DB.  The issue is when you select a query that does not have matching data to it and it is being summed as null looks like.
0
LowfatspreadCommented:
it is the way aggregate functions work,,,

if you don't want the null row returned then use a having clause , without a group by to resolve your issue,,,


SELECT  sum(segpax) segpax
FROM jismit_TCNdetail
WHERE
(issdate between '20080801' AND '20080831') AND
(fltdate between '20080801' AND '20090831') AND
(oddst = 'xxx') AND
(dmtseg = 'Y')
having sum(segpax) is not null
0
sammySeltzerCommented:
i will look at the value of issdate and see if it is the same as any of the hardcoded date values.
0
CPOINT2000Author Commented:
I am attaching a screen print.  Top images (1), is where there is data not found in DB with and Group By makes a difference on the output.
Bottom image (2) is when the query has data in DB, Group by does not matter whether used or not.  

I can use Having, but would rather not.  Sounds like I will force the user to select a display item that will force the Group By (already designed this way).

As I said above, I am trying to find out why image 1 and image 2 works the way they do (difference is one has matching data, other does not).

0
CPOINT2000Author Commented:
sorry forgot the image :)
sqlexample.jpg
0
sammySeltzerCommented:
I will suggest you try casting the hard coded date values.



SELECT  segpax
FROM jismit_TCNdetail 
WHERE 
(issdate between cast('20080801' as datetime) AND cast('20080831' as datetime) AND 
(fltdate between cast('20080801' as datetime) AND cast('20090831' as datetime) AND 
(oddst = 'xxx') AND 
(dmtseg = 'Y') ;

Open in new window


You may do same fore issdate
0
LowfatspreadCommented:
IT IS THE STANDARD EXPECTED RESULT
albeit there are two possible causes... either no rows match your criteria or
all rows have a null column value....

if you don't want to specifically code a having clause when no group by is specified and you cannot present the user with a NULL return then you have to del with it in your code...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CPOINT2000Author Commented:
I do not think it is a date issue.  If I do the following where (simplest level) 3 characters defined, I get same result.  If matching data, 1 line display, if no matching data 1 line null display

SELECT  sum(passenger) passenger
FROM DBTABLE
WHERE DEST= 'XXX'

I think I have to use group by, that's pretty much is the answer.

Thanks all for suggestions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.