How to Count Columns With the Same Value in a Specific Row in MySQL

How to count columns with the same value in a specific row in Mysql?

What finally did was a general query and then fetch and save every column value into a variable. Then i just added them, because the value of each column could be 0 or 1. Here is the code:

$query = mysqli_query($con, "SELECT * FROM registers WHERE ID = '$ID'");
$result = mysqli_fetch_array($query);

$column1 = $result['column1'];
$column2 = $result['column2'];
$column3 = $result['column3'];

$total = $column1 + $column2 + $column3;

If the column has a value = 0, will not be considered into the count.

How to count rows of a column by depending on same values of another column?

Check if this is what you are looking for and if it helps you-

mysql> select semester_id, count(unit_id) from tbl_enrolments group by semester_id;
+-------------+----------------+
| semester_id | count(unit_id) |
+-------------+----------------+
|           2 |              5 |
|           1 |              8 |
|          12 |              2 |
+-------------+----------------+
3 rows in set (0.00 sec)

mysql> select semester_id, unit_id, count(unit_id) from tbl_enrolments group by semester_id, unit_id;
+-------------+---------+----------------+
| semester_id | unit_id | count(unit_id) |
+-------------+---------+----------------+
|           2 |       3 |              3 |
|           2 |       1 |              2 |
|           1 |       2 |              2 |
|           1 |       6 |              2 |
|           1 |       4 |              4 |
|          12 |       1 |              1 |
|          12 |       6 |              1 |
+-------------+---------+----------------+
7 rows in set (0.00 sec)

mysql> select semester_id, unit_id, count(unit_id) from tbl_enrolments group by semester_id, unit_id having semester_id = 2;
+-------------+---------+----------------+
| semester_id | unit_id | count(unit_id) |
+-------------+---------+----------------+
|           2 |       3 |              3 |
|           2 |       1 |              2 |
+-------------+---------+----------------+
2 rows in set (0.00 sec)

mysql> select semester_id, unit_id, count(unit_id) from tbl_enrolments group by semester_id, unit_id having semester_id = 2 and unit_id = 3;
+-------------+---------+----------------+
| semester_id | unit_id | count(unit_id) |
+-------------+---------+----------------+
|           2 |       3 |              3 |
+-------------+---------+----------------+
1 row in set (0.00 sec)

SQL count rows with same value in column and group by id?

You need to count the value of the column value for each id:

select 
  t.id,
  (select count(*) from tablename where value = t.value) count
from tablename t 

See the demo

or:

select t.id, g.count
from tablename t inner join (
  select value, count(value) count
  from tablename 
  group by value
) g on g.value = t.value

See the demo

Mysql count number of rows which have same value

You need group by and count

 select your_column_name, count(*) from your_table 
 group by your_column_name;

in your case assuming status is th column name

select concat("there is " , count(*), "  status in your db") from your_table
group by status;

MySQL count columns on specific value

You can do this using SUM and CASE:

select user_id,
  sum(case when product_id = 1 then 1 else 0 end) as prod_1_count,
  sum(case when product_id = 2 then 1 else 0 end) as prod_2_count,
  sum(case when product_id = 3 then 1 else 0 end) as prod_3_count,
  sum(case when product_id = 4 then 1 else 0 end) as prod_4_count
from your_table
group by user_id

how to count same value as one mysql?

If I understand your requirements correctly, you need to use the distinct keyword, which will count duplicates only once.

select  count(distinct order_id)
from    yourTable

Further documentation on this functionality is available here

SELECT rows having same value in two fields, and different value in another

I want to select rows that have the same value in column1 and column2 but differ in column3.

Use exists:

select t.*
from my_table t
where exists (select 1
              from my_table t2
              where t2.column1 = t.column1 and t2.column2 = t.column2 and
                    t2.column3 <> t.column3
             );

Your sample results, though, suggest that you just want column1/column2 pairs. If so:

SELECT column1, column2
FROM my_table
GROUP BY column1, column2
HAVING MIN(column3) <> MAX(column3);

Your version of the query just returns column1/column2 pairs that have a column3 value with more than one row.

---

Related Topics