CODEWITHSUNDEEP
mysqlcountsum
user3209031
user3209031

Reputation: 835

Count considering sum of 3 columns

I have 3 column (prod1 , prod2 , prod3 ) with TYPE : DOUBLE

id |  prod1  |  prod2  | prod3  |
1  |   1.3   |   2.6   |  2.8   |
2  |   0.8   |   3.4   |   0    |
3  |    0    |    0    |  1.3   |
4  |    0    |    0    |   0    |

What I want is COUNT() of 3 columns 

SELECT count(prod1,prod2,prod3) AS allc 
  FROM `testprd` 
 WHERE id =3

I know above code is wrong

SHOULD GIVE RESULT 

allc
-------
 1

As prod1 and prod2 have 0 values

Similarly when id = 4 count should be 0 as all column for resp id have zero value ,but when id = 1 then count should be 3

Hence I taught count for each id columns and then sum of all , will result me solution but am not able to reach it.

BELOW IS WHAT I HAVE TRIED 

SELECT count(prod1) AS a, 
       count(prod2) AS b, 
       count(prod3) AS c 
  FROM `testprd` 
 WHERE id =3

Result:

a |  b   |  c 
-------------
1    1     1

But should be: 

a |  b   |  c 
-------------
0    0     1

So sum(a+b+c) = 1

Hence count for id = 3 is 1

What am I doing wrong?

Upvotes: 0

Views: 67

Answers (6)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Use a simple query like this

SELECT
    IF(prod1 > 0,1,0)+IF(prod2 > 0,1,0)+IF(prod3 > 0,1,0) as Total
FROM test
WHERE id = 3;

SQL Fiddle Demo

OUTPUT

| TOTAL |
|-------|
|     1 |

Upvotes: 1

wedi
wedi

Reputation: 1422

COUNT counts the rows slected by your SELECT statement, it does not sum up the column values.

SELECT prod1 + prod2 + prod3 AS mySum 
    FROM `testprd` 
    WHERE id =3;

See the MySQL doc concerning Arithmetic Operators and COUNT

Upvotes: 0

Fenistil
Fenistil

Reputation: 3801

Use

SELECT id, if(prod1+prod2+prod3>0,1,0) from testprd;

For all columns separated it should be:

SELECT id, if(prod1>0,1,0), if(prod2>0,1,0), if(prod3>0,1,0) from testprd;

Upvotes: 1

Mithrandir
Mithrandir

Reputation: 25357

How about:

       SELECT 
          count(*) AS allc FROM `testprd` 
       WHERE 
          id =3 AND 
          0 < ANY (prod1, prod2, prod3);

Upvotes: 0

Jorge Campos
Jorge Campos

Reputation: 23371

Just check if that product is different from zero then sum all counts like:

 SELECT if(prod1!=0,1,0) +  
        if(prod1!=0,1,0) +, 
        if(prod1!=0,1,0) AS ct 
   FROM `testprd` 
  WHERE id =3

Upvotes: 0

VMai
VMai

Reputation: 10336

You could get the result you want to have with

SELECT  
    (prod1 !=0 ) + (prod2 != 0) + (prod3 != 0) AS allc 
FROM `testprd` 
WHERE id = 3

The aggregate function COUNT counts rows in a table or not null rows in a certain column, but not the values that are not equal zero in a set of columns.

COUNT(expr)

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.

COUNT() returns 0 if there were no matching rows.

Upvotes: 2

Related Questions