![]() In the context of the arithmetic operator * the logical result of the IS NOT NULL operator is implicitly converted to a number, 1 for True, 0 for False. SUM( (col1 IS NOT NULL) * (col2 IS NOT NULL) ) In particular, you could replace the COUNT with SUM and treat the predicates as numbers (1/0) in an arithmetic expression: SELECT It is possible to shorten the expression somewhat by choosing a less clear syntax. The obvious flaw of this workaround (either variation) is that it is clearly rather unwieldy and will become ridiculously long very quickly as you add more columns to account for. A row will be counted only if neither col1 nor col2 is null. Where instead of the 1 you can put any non-null constant. Or the MySQL-specific IF function: SELECTĬOUNT(IF(col1 IS NOT NULL AND col2 IS NOT NULL, 1, NULL)) It could be either the standard-compliant CASE: SELECTĬOUNT(CASE WHEN col1 IS NOT NULL AND col2 IS NOT NULL THEN 1 END) If that last aspect of the behaviour is what you are trying to achieve, you could emulate it using a conditional inside COUNT. However, one other important point is that a tuple is counted only if none of the individual values in the tuple is null. Monitoring PostgreSQL with Navicat Monitor 3.Obviously, COUNT(DISTINCT) with multiple columns counts unique combinations of the specified columns' values.Trace Queries on your PostgreSQL Instances with Navicat Monitor 3.Viewing PostgreSQL Instance Details in Navicat Monitor 3.A Quick Guide to Naming Conventions in SQL - Part 2.A Quick Guide to Naming Conventions in SQL - Part 3. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |