phpmyadmin Question posted in
The official documentation can be found here.

How to count the number of columns having specific value (lke '1') in PHPMyadmin DB table?

I have a table like below having 3 columns with value ‘1’ or ‘0’.

S.no   Product    Qty1    Qty2    Qty3  
1.     Soap         1      0       1
2.     Ball         1      1       0
3.     Deodrant     0      0       0
4.     Butter       1      0       1

How can I count the total number of ‘1’ in the table like the above in is having 6 nos? Also what if I want to count total rows having only ‘1’ value?

<?php

if($_SERVER['REQUEST_METHOD']=='POST')
{
 include 'Config.php';

 $conn = mysqli_connect($HostName,$HostUser,$HostPass,$DatabaseName);

 if ($conn->connect_error) 
  die("Connection failed: " . $conn->connect_error);

// $sql = "SELECT SUM(Qty1 + Qty2 + Qty3) from Table"; doesn't seems to work


 $result = $conn->query($sql);

 if ($result=mysqli_query($conn,$sql))
  {
  // Return the number of rows in result set
  $rowcount=mysqli_num_rows($result);

  echo $rowcount;
  // Free result set
  mysqli_free_result($result);
  } 

 else
  echo "0";
}

 else
  echo "failed";

 $conn->close();

?>
Tags:

3

Answers


  1. 0

    Hope its helpful…

    SELECT
  sum(Qty1) as sum_Qty1
  , sum(Qty2) as sum_Qty2
  , sum(Qty3) as sum_Qty3
  , sum(Qty1) + sum(Qty2) + sum(Qty3) as tot_Qty 
FROM
  product
    Login or Signup to reply.
  2. 0

    You can do it like this:

    select 
  sum(Qty1 + Qty2 + Qty3) total_ones,
  sum(Qty1 and Qty2 and Qty3) total_rows_with_ones
from tablename

    See the demo.
    Results:

    | total_ones | total_rows_with_ones |
| ---------- | -------------------- |
| 6          | 0                    |
    Login or Signup to reply.
  3. 0

    Using conditional aggregation sum(case when…

    drop table if exists t;
create table t
(Sno int,   Product varchar(10),   Qty1 int,    Qty2 int,    Qty3 int);
insert into t values  
(1  ,   'Soap'       ,  1   ,   0    ,   1),
(2  ,   'Ball'       ,  1   ,   1    ,   0),
(3  ,   'Deodrant'   ,  0   ,   0    ,   0),
(4  ,   'Butter'     ,  1   ,   0    ,   1);


select 
  sum(Qty1 + Qty2 + Qty3) total_ones,
  sum(case when Qty1 = 1 or Qty2 = 1 or Qty3 = 1 then 1 else 0 end) total_rows_with_ones
from t  ;


+------------+----------------------+
| total_ones | total_rows_with_ones |
+------------+----------------------+
|          6 |                    3 |
+------------+----------------------+
1 row in set (0.001 sec)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search