Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm experiencing a problem with a query that I've written.

Given a table with the schema:

create table somevals(division varchar(100), 
                      subdivision varchar(100), 
                      good float, 
                      bad float);

I would like to find, for each unique (division, subdivision), the sum of good values, the sum of bad values, and the ratio of good to bad values. So, I wrote the query:

select division, subdivision, g, b, g / b as ratio
from (select division, subdivision, sum(good) as g, sum(bad) as b 
      from somevals
      group by division, subdivision
     ) as temp

The problem is that this returns far fewer rows than I would expect. However, if I remove the division (g / b as ratio), this query returns the correct number of rows in the result (about three times as many). None of the columns contain Null values. None of the bad values are less than or equal to zero, so sum(bad) will always be non-zero.

I am at a loss for how this could be happening. Under what circumstances can adding a division expression in the select statement reduce the number of rows in the result?

share|improve this question

migrated from stackoverflow.com Mar 16 '13 at 11:27

This question came from our site for professional and enthusiast programmers.

    
No row(s) where b would be 0? –  cheesemacfly Mar 13 '13 at 19:38
1  
@cheesemacfly None, wouldn't that return an error? –  Wilduck Mar 13 '13 at 19:40
2  
@Wilduck Can you replicate your situation on a sqlfiddle? (sqlfiddle.com) –  Lamak Mar 13 '13 at 19:43
1  
It would return an error or NULL, depending on the value of ARITHABORT. Either way, it wouldn't simply exclude rows. ...What happens if you eliminate the outer query, and simply add SUM(good) / SUM(bad) AS ratio to the SELECT list? –  Dan J Mar 13 '13 at 19:43
    
@Lamak Unfortunately, I can't. This is occurring in my company's database with a query I've written. I made a fiddle to see if I could replicate it, but It works just fine there: (sqlfiddle.com/#!3/7ea8c/6) –  Wilduck Mar 13 '13 at 19:45

1 Answer 1

You simply cannot change the number of rows returned by changing the SELECT clause. The problem must lie elsewhere.

If you're absolutely sure that's what's happening, I would run DBCC on the table to make sure it's not corrupted.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.