007mrviper 007mrviper - 6 months ago 8
SQL Question

Output is "1" even if the column is empty for some entries in MySQL database

I have entries on database as shown in the image:

enter image description here

SQL query is:

SELECT Lastname, COUNT(Item) FROM info GROUP By Lastname;


The output is:

enter image description here

Why does it returns "1" even if the "Item" column is blank for the highlighted entries?

Answer

Below query will give the required result :-

select Lastname,count(CASE item WHEN '' THEN NULL ELSE item END) item from info  group by Lastname;

The reason why you were getting count as 1 for blank is that your data type may be varchar or char.If your data type were int then it must have given count as 0 for blank value.