CODEWITHSUNDEEP
phpmysql
ssdesign
ssdesign

Reputation: 2821

How to filter mysql rows in PHP where data is either empty or 0

I am trying to find is there is an efficient way to do a query which can ignore rows of data where any column has a value of 0 or empty

My current query looks like this:

$query = "SELECT AVG(concept1) AS c1, AVG(concept2) AS c2, AVG(concept3) AS c3, AVG(concept4) AS c4, AVG(concept5) AS c5, AVG(concept6) AS c6 FROM KeyPad";

Now I dont want to count rows while averaging where any column data is 0 or null.

eg.

-----------------------------------------
|concept1  | concept2  | concept3  | ...
-----------------------------------------
| 1.2      |   3.2     |    0      | ...
| 2.4      |   4.1     |    2.1    | ...
| 3.2      |   5.1     |    2.2    | ...
| 4.3      |   3.2     |    5.3    | ...
| 1.2      |   0       |    3.2    | ...
------------------------------------------

When I run my query, I only want to generate average using data from rows 2,3,4 and ignore all data from rows 1 and 5.

I read that I can use something like COALESCE but not sure if it can work for averaging values.

Any suggestions which direction should I explore?

Additional Notes (updated):

I may not have explained correctly. When I say ignore rows, I mean if one item inn the row is 0, I want to ignore all the other items in the rows while averaging those columns too.

So in the example table above, the average for Column1 will be: (2.4+3.2+4.3) / 3 because First row in column 3 is 0 and last row in column 2 is also 0. So other columns will also need to ignore these two rows while calculating the average.

I hope that clarifies my problem.

Upvotes: 6

Views: 1358

Answers (3)

Jigar Shah
Jigar Shah

Reputation: 6223

You can use like this, it will not consider row with value 0 and AVG itself excludes null values

SELECT AVG(NULLIF(concept4, 0)) FROM KeyPad;

or using case (sample demo link)

SELECT AVG(case  
              when concept1 = 0 then null
              else concept1
           end) as c1
FROM KeyPad

so your full query:

SELECT AVG(NULLIF(concept1,0)) AS c1,
       AVG(NULLIF(concept2,0)) AS c2,
       AVG(NULLIF(concept3,0)) AS c3,
       AVG(NULLIF(concept4,0)) AS c4,
       AVG(NULLIF(concept5,0)) AS c5,
       AVG(NULLIF(concept6,0)) AS c6
FROM KeyPad

If you want you can add condition in WHERE similar like this according to your requirement:

SELECT AVG(concept1) AS c1
FROM KeyPad
WHERE concept1 > 0

Note: Where condition may change your desired output if grouped wrongly with more than one column. If you want to check for individual field then use NULLIF or CASE

Upvotes: 3

Purushottam zende
Purushottam zende

Reputation: 552

You can try like this also

$query = "SELECT 
            sum(concept1) AS c1, sum(if(concept1 is not null or concept1 != '',1,0 )) as c1_count,
            sum(concept2) AS c2, sum(if(concept2 is not null or concept2 != '',1,0 )) as c2_count
        FROM 
            KeyPad";

And on front end you can do the Division

OR

$query = "SELECT 
            ( sum(concept1) / sum(if(concept1 is not null or concept1 != '',1,0 )) ) as c1,
            ( sum(concept2) / sum(if(concept2 is not null or concept2 != '',1,0 )) ) as c2
        FROM 
            KeyPad";

Upvotes: 0

Ahmed Ginani
Ahmed Ginani

Reputation: 6650

If I am getting you right, Below query will help you:

$query = "SELECT AVG(concept1) AS c1, AVG(concept2) AS c2, AVG(concept3) AS c3 FROM KeyPad where (concept1 IS NOT NULL OR concept1 != 0) AND (concept2 IS NOT NULL OR concept2 != 0) AND (concept3 IS NOT NULL OR concept3 != 0");

Here Just add till concept3 you can do with more columns.

Upvotes: 1

Related Questions