Clever Geek Handbook

How do I select the number of rows multiplied by non-null columns? (MySQL)

Suppose I have a table:

col1, col2, col3
1     0     1.3
0     0     0
0     1     0
1     1.5   1

Now let's say that each row has a "weight" calculated as follows:

(col1 > 0 ? 1 : 0) + (col2 > 0 ? 1 : 0) + (col3 > 0 ? 1 : 0)

How can I choose the total weight of all rows?

With the data I gave, the total weight is 2 + 0 + 1 + 3 = 6

+3


source to share


1 answer


You only need one machine SUM()

that surrounds all conditions without GROUP BY

.

SELECT
  SUM(
   (CASE WHEN col1 > 0 THEN 1 ELSE 0 END)
   + (CASE WHEN col2 > 0 THEN 1 ELSE 0 END)
   + (CASE WHEN col3 > 0 THEN 1 ELSE 0 END)
  ) AS total_weight
FROM your_table

http://sqlfiddle.com/#!2/b0d82/1



I am using CASE WHEN...

here as it is portable across any DBMS. Since MySQL returns a boolean value of 0 or 1 for conditions, this can be simplified in MySQL as:

SELECT
  SUM(
   (col1 > 0) /* returns 1 or 0 */
    + (col2 > 0)
    + (col3 > 0)
   ) AS total_weight
FROM your_table

http://sqlfiddle.com/#!2/b0d82/2

+5


source






More articles:


All Articles