Questions about the Count sql command

Asked

Viewed 596 times

4

When I write the command select count(carga > 40) from cursos; what counting logic the command count is running?

This command worked on mysql however it returned me a result that I could not identify the source. I could not understand what the Count identified on the basis.

sql

  • Basically he counted how many load lines > 40 gave null (which in practice returned how many times the load field is null, because null > 40 == null in the same way). I gave two alternatives in my answer, if you have questions comment, I elaborate better. Even you.

3 answers

4

The count(expressao) doesn’t fit what you’re trying to.

It returns does not count "true" or "false", but "how many occurrences of expressão are not null.

The count(*) is an exception, it returns all lines, regardless of nullity.

Handbook:

https://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_count


Two solutions to your case: 

SELECT COUNT(*) FROM tabela WHERE carga > 40;

or

SELECT SUM(IF(carga > 40, 1, 0 ));


This second, with if, can be understood better in this post:

/a/127134/70

1

Count() returns the line numbers. For example we found 5 records with cargo > 40, then he will return 5.

To add the value of the 5 records for example use sum().

1

I’d do it this way 

select count(*) from nometabela where carga > 40;

In this example it will count the records that have been selected The selected records would be all that the value of carga was greater than 40

 select count(carga) from nometabela where carga > 40;

put carga instead of * may have problems if charge is null

Browser other questions tagged sql

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