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?
 
LowfatspreadConnect With a Mentor Commented:
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
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
sammySeltzerCommented:
Nice catch there HainKurt!
0
 
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
 
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
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.

All Courses

From novice to tech pro — start learning today.