Does Count(*) Always Return a Result

Does COUNT(*) always return a result?

Yes, because it's an aggregate and returns zero.
Unless you add GROUP BY in which case no result because there is no group...

MAX/SUM etc would return NULL unless you add GROUP BY then no rows.
Only COUNT returns a number for no results

Edit, a bit late: SUM would return NULL like MAX

Edit, May 2013: this applies to all main RDBMS. I guess as per the ANSI standard

When does COUNT(*) return NULL?

It doesn't return NULL. The GROUP BY in your example makes it return no rows at all, which is not the same as a NULL in a column.

Explaining COUNT return value when used without group

When constants are specified in the COUNT expression, SQL Server can optimize the query at compile time an avoid evaluating the expression at execution time. The resultant value can never be NULL in the first 2 queries and will always be NULL in the last 2 queries.

Below is a snippets from the SELECT COUNT(1/0) and SELECT COUNT(1/NULL) execution plans:

The "1 row affected" message generated by the client tool (SSMS), reflecting the rowcount returned by the INSERT statement. If undesired, add SET NOCOUNT ON; to the beginning of the script.

Count always returns 1...but does it exist?

When you use COUNT() in your query you will always get one row returned even if only to tell you the count is zero. You need to check the value of COUNT(reviews) to get that value:

$result = mysqli_query($sqli, "SELECT count(reviews) AS `count` FROM myTable WHERE `category`='$list' AND `reviews`='$php_file_name'");
$check = mysqli_fetch_assoc($result);

if($check['count'] >= 1){

You'll notice that I gave count(reviews) an alias as it makes accessing that value easier in the PHP code.

Why count doesn't return 0 on empty table

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.

Can Count(*) ever return null?

No, it will only return a zero (or non-zero) result. SqlServer will not return null.

SQL query does not return the correct count

Your query is returning one row with one value (the count of rows), so dt.Rows.Count is correctly returning 1 (meaning 1 row returned by your query).
Using a datatable, you would find the count returned by your query at dt.Rows[0][0] (row 0 column 0 of your datatable)

Count case is returning a wrong result

Instead of counting both 1 and 0. All you have to do is NOT count both.

The thing is, a count by value doesn't count the NULL's.

But when the CASE WHEN only returns values 0 or 1, the COUNT behaves no different from a COUNT(*). Since it doesn't return any NULL's for the COUNT to ignore.

So just remove those ELSE 0 and it'll be fine.

Btw, using alias names can shorten your SQL.

SELECT
    OUTLETNAME,
    COUNT(ORDERID) AS ORDERSCOUNT,
    COUNT(DISTINCT(s.CUSTOMERID)) AS CUSTOMERCOUNT,
    COUNT(CASE 
          WHEN DATEDIFF(mi, s.ORDERDATE, s.CLOSEDDATE) < 30 AND s.STATUSID = 'CLOSED'   
          THEN 1
          END) [LESS 30 (CLOSED)],
    COUNT(CASE 
          WHEN DATEDIFF(mi, s.ORDERDATE, s.CLOSEDDATE) > 30 AND s.STATUSID = 'CLOSED'   
          THEN 1
          END) [Greater 30 (CLOSED)]
FROM dbo.VW_SALES_SUM s
JOIN dbo.[OUTLET] o ON o.CODE = s.OUTLETCODE
WHERE 
    s.ORDERDATE BETWEEN '7/3/2018 11:00:00 AM' AND '7/4/2018 02:00:00 AM'
GROUP BY OUTLETNAME, o.BRAND
ORDER BY COUNT(ORDERID) DESC

You could also just replace the COUNT by a SUM. But a COUNT has the advantage that you can combine it with a DISTINCT to only count the unique values that aren't NULL.

For example:

SELECT COUNT(DISTINCT CASE WHEN col1='bar' then col2 END) AS Bars FROM Foo;

Sql count query always return 1

You're simply creating a cross product between the rows in the two tables, and then counting the number of rows in the resulting cross product. You need to use separate queries to calculate each count:

SELECT (SELECT COUNT(*) FROM employees WHERE gender = false)/
       (SELECT COUNT(*) FROM citizens WHERE gender = false)

---

Related Topics