mysql count distinct statistical results deduplication

The content of this article is about deduplication of mysql count distinct statistical results. It has certain reference value. Friends in need can refer to it. , hope it helps you.

Related mysql video tutorial recommendations: "mysql tutorial"

1. Use distinct to remove duplicates (suitable for querying the total number of the entire table) There are multiple schools ＋For teacher submissions, the total number of authors needs to be counted. select count(author) as total from files. Each author has submitted a lot of contributions, and there are duplicate records here.

select distinct author from files;It is possible that teachers from two schools have the same name, but only one will be counted, which is an error. Select distinct author, sid from files statistics (author + school id) combined unique value, the result is the correct result, but how to know how many people there are? select count(distinct author,sid) as total from files

2, group by group deduplication (suitable for querying the total number of each group after grouping according to conditions)

select author, count(distinct id) from files group by sid

3, record two tables The sum of the numbers, these two tables are queried separately

SELECT SUM(c) 
FROM
  (SELECT COUNT(DISTINCT from_user_id, message_id) c
  FROM im_message 
  WHERE dr = 0  AND message_status = 2  AND user_type = 1  AND to_user_id = 2 
  UNION ALL 
  SELECT COUNT(DISTINCT group_id, message_id) c
  FROM im_messagerefgroup 
  WHERE dr = 0  AND user_id = 2
  ) 
AS temp ；

This article ends here. For more knowledge about MySQL, you can pay attention to MySQL Tutorial on the php Chinese website. Column! ! !

The above is the detailed content of mysql count distinct statistical results deduplication. For more information, please follow other related articles on the PHP Chinese website!