852 votes
15 answers
php - How to count the number of columns having specific value (lke '1') in PHPMyadmin DB table?
Get the solution ↓↓↓
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();
?>
Undefined asked
213
votes
Answer
Solution:
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 |
|
83
votes
Answer
Solution:
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
270
votes
Answer
Solution:
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 ;
+
Share solution ↓
Additional Information:
Date the issue was resolved:
Link To Answer People are also looking for solutions of the problem: sqlstate[23000]: integrity constraint violation: 1452 cannot add or update a child row: a foreign key constraint fails
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.