对name字段应用Count distinct函数会得到六个不同的名称:
SELECT Count(DISTINCT name)
FROM employees;
FROM employees;
当然,也可以给出一列用逗号分隔的表达式。假若这样,COUNT()将返回非空值的不同组合数目。以下查询将统计哪些姓名和工资都非NULL的不同记录的数目。
SELECT Count (DISTINCT name, salary)
FROM employees;
FROM employees;
我们还可以使用group by子句计算每组中重复数据的数量。下面的查询将用来统计不同部门中重名的情况:
SELECT dept_id,
COUNT(*) - COUNT(DISTINCT name) AS 'duplicate names'
FROM employees
GROUP BY dept_id;
COUNT(*) - COUNT(DISTINCT name) AS 'duplicate names'
FROM employees
GROUP BY dept_id;