5

I need to count a table's rows but I was prompt with an unusual behavior of count(*).

count(*) does not return results when I use a multi column select on an empty table. But returns expected results (0 rows) if I remove the other columns from the select statement (Single column Select).

In the code below you will find multiple tests to show you what I'm talking about.

The structure of the code bellow is:

1) Creation of a table

2) Multi column select on empty table tests, which returns unexpected results

3) Single column select on empty table test, which returns the expected result

4) Multi column select on filled table test, which returns the expected result

Question

Given this results my question is:

Why does the a multi column select on empty table doesn't return 0, and a single column select returns it?

Expected Results definition

Expected results to me means:

if a table is empty, count(*) returns 0.

If a table is not empty count returns the row count

--CREATE TEST TABLE 

CREATE TABLE #EMPTY_TABLE(
    ID INT
)

DECLARE @ID INT
DECLARE @ROWS INT

--MULTI COLUMN SELECT WITH EMPTY TABLE 

--assignment attempt (Multi-column SELECT)
SELECT @ID = ID, @ROWS = COUNT(*) 
FROM #EMPTY_TABLE

--return Null instead of 0
SELECT @ROWS Test_01 , ISNULL(@ROWS, 1 )'IS NULL'

--Set variable with random value, just to show that not even the assignment is happening
SET @ROWS = 29

--assignment attempt (Multi-column SELECT)
SELECT @ID = ID, @ROWS = COUNT(*) 
FROM #EMPTY_TABLE

--return 29 instead of 0
SELECT @ROWS Test_02

--SINGLE COLUMN SELECT WITH EMPTY TABLE 

--assignment attempt (Single-column SELECT)
SELECT @ROWS = COUNT(*)
FROM #EMPTY_TABLE

--returns 0 the expected result
SELECT @ROWS Test_03

--MULTI COLUMN SELECT WITH FILLED TABLE 

--insert a row
INSERT INTO #EMPTY_TABLE(ID)
SELECT 1

--assignment attempt
SELECT @ID = ID, @ROWS = COUNT(*) 
FROM #EMPTY_TABLE

--Returns 1
SELECT @ROWS Test_04
Nelssen
  • 1,023
  • 1
  • 17
  • 42
  • 2
    just a thought: `select ID from #empty_table` will only return a result row, if the table has at least one row, however, `select count(*) from #empty_table` (implicit group by) should return a result row, even if there are no table rows. under the assumption that this is a valid query in sybase, I'd assume the results of those two queries are joined to answer your query, since the first query produces no rows, the combined query will also produce no result, hence no assignments. but ... disclaimer: I have no experience whatsoever with sybase. – Jakumi Mar 14 '19 at 09:42
  • thank you for you input Jakumi. I actually don't know if this behavior also happens in other databases like mysql or sqlserver. but as per my understanding count(*) should return results even when there are no rows. there are conditions where this don't applies which is when using group by (specific case), but this is not the case – Nelssen Mar 14 '19 at 09:53
  • Interesting. So Sybase allows that a non-aggregated column isn't included in the group by. Btw, there's that same effect in the T-Sql for MS Sql Server. Test [here](https://rextester.com/JYXW42725). Except that a GROUP BY is required then. – LukStorms Mar 14 '19 at 10:09
  • the explicit GROUP BY only specifies groups to be used for aggregation, default is all rows belonging to one group, requiring an explicit group by is usually just to properly define the columns when there are multiple values for ungrouped columns (possible). – Jakumi Mar 14 '19 at 10:19
  • Extra test: add another ID to that table. I expect the result to be two records, each with a count of 1 – Hans Kesting Mar 14 '19 at 10:25

3 Answers3

4

So I read up on the grouping mechanisms of sybase, and came to the conclusion, that in your query you have a "Transact-SQL extended column" (see: docs on group by under Usage -> Transact-SQL extensions to group by and having):

A select list that includes aggregates can include extended columns that are not arguments of aggregate functions and are not included in the group by clause. An extended column affects the display of final results, since additional rows are displayed.* (emphasis mine)

(regarding the *: this last statement is actually wrong in your specific case, since one rows turn into zero rows)

also in the docs on group by under Usage -> How group by and having queries with aggregates work you'll find:

The group by clause collects the remaining rows into one group for each unique value in the group by expression. Omitting group by creates a single group for the whole table. (emphasis mine)

So essentially:

  1. having a COUNT(*) will trigger the whole query to be an aggregate, since it is an aggregate function (causing an implicit GROUP BY NULL)
  2. adding ID in the SELECT clause, will then expand the first group (consisting of no rows) into its contained rows (none) and join it together with the aggregate result columns.

in your case: the count is 0, since you also query for the id, for every id a row will be generated to which the count is appended. however, since your table has no rows, there are no result rows whatsoever, thus no assignments. (Some examples are in the linked docs, and since there is no id and an existing id must be in the id column of your result, ...)

to always get the count, you should probably only SELECT @ROWS = COUNT(*) and select ids separately.

Jakumi
  • 8,043
  • 2
  • 15
  • 32
  • 1
    Great Search @Jakumi, unfortunately this specific example is not displayed in the links, but they give a pretty much good Idea of what could happen in this specific case. After reading I reach the conclusion that the group is created with no rows, and because it is expanded it will be joined with the table that has no rows, and the result of (inner) joining an empty table with another table is an empty table. Thanks for the referenced links. – Nelssen Mar 18 '19 at 08:50
  • 1
    @Nelssen - Right conclusion from wrong arguments ;-). Your issue has nothing to do with `JOIN` (so to speak), but everything with `GROUP BY`. As Jakumi wrote: there is nothing to group on for the `COUNT()`, so by definition nothing can be displayed. That's how it works. You're correct that this may be caused by join to the empty tables, but even if there's nothing, `COUNT()` returns something, unless there's a `GROUP BY` in the mix. – AcePL Mar 29 '21 at 13:53
0

If you are counting rows and trying to get ID when there are no rows - you need to check if they EXISTS. Something like this:

SELECT COUNT(*), 
    (CASE WHEN EXISTS(SELECT ID FROM EMPTY_TABLE) THEN (SELECT ID FROM EMPTY_TABLE) ELSE 0 END) AS n_id 
FROM EMPTY_TABLE

In case with more than 1 row you will get subquery error.

M.N.
  • 65
  • 8
  • Hi @M.N., I don't believe this answers the question – Nelssen Mar 18 '19 at 08:36
  • @Nelssen, if you have nothing to return - query will return... nothing (not NULL). COUNT and EXISTS returning not original data from table, but with COUNT 0 if no data, and with EXISTS everything what you need. Just try smth like this: SELECT ID / 100 AS FROM EMPTY_TABLE WHERE ID = 999 [nothing]. And SELECT 100 / (SELECT g1 FROM EMPTY_TABLE WHERE ID = 999) [NULL]. So in your case query need to return something, if you want use it with count. – M.N. Mar 18 '19 at 11:48
  • Hi M.N., Thanks for your efforts, this is not the answer to "Why we need to do this workaround". This solution only works for very specific cases, which are with 0 and 1 rows. I would up-vote it if it worked in all scenarios (0, 1, or many rows), but cannot do it as it is now. Make sure you provide more sustainable solutions next time :) – Nelssen Mar 25 '19 at 14:34
0

This query:

SELECT @ID = ID, @ROWS = COUNT(*) 
FROM #EMPTY_TABLE

The problem is that COUNT(*) makes this an aggregation query, but you also want to return ID. There is no GROUP BY.

I suspect your ultimate problem is that you are ignoring such errors.

This SQL Fiddle uses SQL Server (which is similar to Sybase). However, the failure is quite general and due to a query that would not work in almost any database.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Even with group by result will be the same. And you are right - he is ignoring error. – M.N. Mar 14 '19 at 11:25
  • Hi @Gordon Linoff, I was suspecting this was a general behavior but have not tested it. That's why I really wanted to understand this behavior. Which errors am I ignoring? if there is no errors being displayed? I cannot ignore an error if it does not exist. What I could be ignoring is some behavior, not errors, as they are none in the cases I presented. – Nelssen Mar 18 '19 at 08:34