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.
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
Now see what happens when you forget to specify FROM clause
SELECT COUNT(*) #customers
What do you think the result is?
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)
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
For that use SUM and IF:
SUM(IF col1=testval1, 1, 0) AS CountTest1
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.
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
=================
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