output a number — the number of students whose name (first_name) matches the name of at least one ot-CodePudding

Well, that is, there are two Johns and one Quill, you need to output the number of those people who have the same name.Well, that is, in one column there should be the total number of students with the same names

SELECT COUNT(id) as count
FROM student
GROUP BY LOWER(first_name) HAVING COUNT(LOWER(first_name)) > 1;

it will output for each name the count, how to make the total?

CodePudding user response：

In order to get the total, select from your query result and add the counts up.

SELECT SUM(cnt)
FROM
(
  SELECT COUNT(*) AS cnt
  FROM student
  GROUP BY LOWER(first_name)
  HAVING COUNT(*) > 1
) counts;

CodePudding user response：

Please, try the following:

SELECT SUM(COUNT(id)) as Total
FROM student
GROUP BY LOWER(first_name) HAVING COUNT(LOWER(first_name)) > 1;