SQL SERVER – COUNT, FROM and a Query – Interesting Observation

In this blog post today we will see an interesting observation which is related to COUNT and FROM clause in a single query. During my recent Comprehensive Database Performance Health Check, this was brought to my attention by one of the attendees.

SQL SERVER - COUNT, FROM and a Query - Interesting Observation count3

When you write code, it is important to review your code. Usually, SQL developers have short deadlines and start writing code with hurry. If there are syntax problems with the code, you will get to know them immediately after executing the code. But there are a few cases where you wanted to mean something but you would get a different result if you miss out the keyword FROM.

Let me show you a simple example

Create the following data set

CREATE TABLE #customers 
(cust_id INT, cust_name VARCHAR(200), contact_no VARCHAR(20))
INSERT INTO #customers
SELECT 10001,'Allen','918762345' UNION ALL
SELECT 10002,'Johnson','918923450' UNION ALL
SELECT 10001,'Victor','912387659'

Suppose you want to find out the total rows from the table #customers. You write this following query

SELECT COUNT(*) FROM #customers

Which results to 3

SQL SERVER - COUNT, FROM and a Query - Interesting Observation count1

Now see what happens when you forget to specify FROM clause

SELECT COUNT(*) #customers

What do you think the result is?

SQL SERVER - COUNT, FROM and a Query - Interesting Observation count2

You may be expecting an error as FROM clause is missing. This is quite interesting that it does not throw any error but an unexpected result of 1. #customers become column alias and count(*) return 1. A simple aggregate of count(*) without counting from any table will result to 1.

Reference: Pinal Dave (https://blog.SQLAuthority.com)

SQL Function, SQL Scripts, SQL Server
Previous Post
SQL SERVER – ERROR: Failed to verify the Authenticode signature of FileName
Next Post
SQL SERVER – How to Generate Random Password? – Enhanced Version – Part 2

Related Posts

5 Comments. Leave new

  • Its possible count for Multiples where statements?

    Where custumersID > 100 and custumersName like ‘john’

    Exemplo of Result

    custumersID | custumersName
    3000 | 4300

    Reply
  • Also try using a non-existent column name in the WHERE clause. Yet another reason to always include the table name (or alias) and not just the column name by itself.

    Reply
  • I have to filter 800 columns.

    the objective is to count the amount of registration that each of the clauses where would have, being regressive to each column.

    The query below shows the complexity error, with the last line having all the previous ones.

    Some other solution to count the items that are filtered.

    ==================
    Declare @Maior int, @Menor int
    Set @Maior=3
    Set @Menor=2

    Select Count(*) As TotalCount,

    Count(Case When A.[BZ] > (B.[BZ] – (C.[BZ] * @Menor) ) and A.[BZ] (B.[BZ] – (C.[BZ] * @Menor) ) and A.[BZ] (B.[CA] – (C.[CA] * @Menor) ) and A.[CA] (B.[BZ] – (C.[BZ] * @Menor) ) and A.[BZ] (B.[CA] – (C.[CA] * @Menor) ) and A.[CA] (B.[CB] – (C.[CB] * @Menor) ) and A.[CB] < (B.[CB] + (C.[CB] * @Maior) ) Then 1 End) As CountAfterCA,

    '– etc AHZ

    from [Quality] as A , [Q _media] as B , [Q_desvio] as C
    =================

    Reply
  • Correct query

    Declare @Maior int, @Menor int
    Set @Maior=3
    Set @Menor=2

    Select Count(*) As TotalCount,

    Count(Case When A.[BZ] > (B.[BZ] – (C.[BZ] * @Menor) ) and A.[BZ] (B.[BZ] – (C.[BZ] * @Menor) ) and A.[BZ] (B.[CA] – (C.[CA] * @Menor) ) and A.[CA] (B.[BZ] – (C.[BZ] * @Menor) ) and A.[BZ] (B.[CA] – (C.[CA] * @Menor) ) and A.[CA] (B.[CB] – (C.[CB] * @Menor) ) and A.[CB] < (B.[CB] + (C.[CB] * @Maior) ) Then 1 End) As CountAfterCA,

    — etc

    from [Quality] as A , [Q _media] as B , [Q_desvio] as C

    Reply

Leave a Reply