MariaDBで重複を除いてカウントをするサンプル
環境
MariaDB 10.6.4
書式
SELECT count(DISTINCT 重複を除くカラム) FROM tablename名
使用例
1.重複している「depname」を除いてカウントします。
MariaDB [testdb]> select * from deptbl;
+-------+---------------+
| depid | depname |
+-------+---------------+
| 1001 | sysengineer |
| 1002 | sysengineer |
| 1003 | sysengineer |
| 1003 | inforengineer |
+-------+---------------+
4 rows in set (0.000 sec)
MariaDB [testdb]> select * from deptbl;
+-------+---------------+
| depid | depname |
+-------+---------------+
| 1001 | sysengineer |
| 1002 | sysengineer |
| 1003 | sysengineer |
| 1003 | inforengineer |
+-------+---------------+
4 rows in set (0.000 sec)
MariaDB [testdb]> select * from deptbl; +-------+---------------+ | depid | depname | +-------+---------------+ | 1001 | sysengineer | | 1002 | sysengineer | | 1003 | sysengineer | | 1003 | inforengineer | +-------+---------------+ 4 rows in set (0.000 sec)
2.重複している「depname」を除いてカウントします。
MariaDB [testdb]> SELECT count(DISTINCT depname) FROM deptbl;
+-------------------------+
| count(DISTINCT depname) |
+-------------------------+
| 2 |
+-------------------------+
1 row in set (0.049 sec)
MariaDB [testdb]> SELECT count(DISTINCT depname) FROM deptbl;
+-------------------------+
| count(DISTINCT depname) |
+-------------------------+
| 2 |
+-------------------------+
1 row in set (0.049 sec)
MariaDB [testdb]> SELECT count(DISTINCT depname) FROM deptbl; +-------------------------+ | count(DISTINCT depname) | +-------------------------+ | 2 | +-------------------------+ 1 row in set (0.049 sec)