Group By - Count returns no rows

Hi everyone:

I guess this should be a simple question but have not been able to find the answer, does anyone know how to make a SQL Sentence to return at least one row when counting?

SELECT COUNT(Id_Field), Field2 FROM Table1 WHERE Code_Field = 1 GROUP BY Field2

This will return 0 rows when the where criteria is not matched by any record on the Table1, but I would like to have one row counting 0 rows, in stead it returns no rows at all.

Thanks for any help.

Since its just a count, you can check for @.@.ROWCOUNT after the SELECT. Or get the count into a variable which is set to 0 by default. so your variable will always have a value.

|||

Interesting problem, I too thought at first glance that it would give you 1 row with value 0, but I tried it on one of my tables with the same results.

I think the problem is that WHERE clause, since there are no instances of Field2 that satisfy the GROUP BY there can be no rows returned at all.

I think the Dinakar is right, you'll have to check @.@.rowcount or something

|||

IF EXISTS( {Your query} ) BEGIN {Your query} END ELSE BEGIN SELECT 0,NULL AS Field2 END

|||

Thanks Motley, I came to this solution too but I thought that a better one could show up, sometimes you expect something more complicated and "elegant" shows up in the way, when the simple solution is in fact the way out of it. Thanks again!