Difference between using COUNT in column and COUNT with "*"

Asked

Viewed 30 times

1

I accomplished 2 querys in a bank where I needed to know the amount of records based on a column but if I change the parameter of COUNT() the results are different.

SELECT COUNT(`time`) as 'tempo' 
from db.table 
WHERE `time` <= 3000 
AND (`created_at` BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND NOW();
// A coluna `time` é do tipo "float" e armazena valores do tipo: 1.000, 0.985, 32.000

SELECT COUNT(*) as 'tempo' 
from db.table 
WHERE `time` <= 3000 
AND (`created_at` BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND NOW();

What’s between using with * and with the column name?

  • I just did a test here, and COUNT only works differently with date fields, if you do for example COUNT(2), will work properly, or even if you put any other field that is not of the type datetime, time, date or timestamp it will work.

  • @Robertodecampos the field time is the type float

  • So fields of the kind float and its derivatives also does not work. Date and time fields also work with floating point so maybe there is the problem.

  • @Maniero believe it is not duplicated and did not understand the explanation of the other topic that Oce marked.

  • In the topic that is marked as duplicate I just gave an answer that explains the difference between the two commands @Rafaelacioly

  • @Davidalves great try, but it got out of context there... I’ll suggest some improvements to the current address of the answer

  • @Rafaelacioly can argue why not? If convince me I reopen.

Show 2 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.