How to Count Rows That Have the Same Values in Two Columns (Sql)

How to count rows that have the same values in two columns (SQL)?

SELECT A,B,COUNT(*)
FROM the-table
GROUP BY A,B

Aggregate Count rows that have the same values in two columns in SQL

You can join the results from the group by query and get the results like this:

SELECT t.A,t.B,C,D,E,F,t1.cnt FROM the_table t
JOIN (SELECT A,B,COUNT(*) cnt FROM the_table GROUP BY A,B) t1
ON t.A = t1.A
AND t.B = t1.B

Here is another way to do it with an aggregate function:

SELECT A,B,C,D,E,F, 
  count(a + b) over(partition by a,b) cnt
FROM the_table t

You can see a demo of both of these methods here:
http://rextester.com/KVDY97918

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)

Finding rows with same values in multiple columns

Try the following:

SELECT A.*
FROM YourTable A
INNER JOIN (SELECT Address, State
            FROM YourTable
            GROUP BY Address, State
            HAVING COUNT(*) > 1) B
ON A.Address = B.Address AND A.State = B.State

Count of duplicate values by two columns in SQL Server

I think this query is what you want:

SELECT SUM(t.cnt)
FROM
(
    SELECT COUNT(*) cnt
    FROM table_name
    GROUP BY number, value
    HAVING COUNT(*) > 1
)t;

I want to count rows of two columns with different values

Aggregate.

SELECT staff_gender, staff_grade
, COUNT(*) AS total
FROM permanent_staff
GROUP BY staff_gender, staff_grade
ORDER BY staff_gender, staff_grade

SQL - select rows that have the same value in two columns

Since you mentioned names can be duplicated, and that a duplicate name still means is a different person and should show up in the result set, we need to use a GROUP BY HAVING COUNT(*) > 1 in order to truly detect dupes. Then join this back to the main table to get your full result list.

Also since from your comments, it sounds like you are wrapping this into a view, you'll need to separate out the subquery.

CREATE VIEW DUP_CARDS
AS
SELECT CARDNUMBER, MEMBERTYPE
FROM mytable t2
GROUP BY CARDNUMBER, MEMBERTYPE
HAVING COUNT(*) > 1

CREATE VIEW DUP_ROWS
AS
SELECT t1.*
FROM mytable AS t1
INNER JOIN DUP_CARDS AS DUP
ON (T1.CARDNUMBER = DUP.CARDNUMBER AND T1.MEMBERTYPE = DUP.MEMBERTYPE )

SQL Fiddle Example

Find rows with duplicate values in two columns where at least one value in one column is a specific value

Use EXISTS:

SELECT p1.*
FROM persons p1
WHERE EXISTS (
  SELECT *
  FROM persons p2
  WHERE p2.ID <> p1.ID
    AND p2.Name = p1.Name AND p2.FirstName = p1.FirstName 
    AND 'UK' IN (p1.Country, p2.Country)
);

See the demo.

---

Related Topics