Most frequently occurring value in sql-CodePudding

I have created a sample table here: https://dbfiddle.uk/wUvTIsyN

I am trying to query the most frequently occurring value with group by. Here's my attempt:

select pid, propName, Address, City, Response, count(Response) as cr
from Prop
group by 
        pid,
        propName,
        Address,
        City,
        Response
order by
      cr
limit 1;

Expected output:

pid propName    Address City    Response

1   p1  addr1   c1  2
2   p2  addr2   c2  3

2 and 3 are most frequently occurring responses.

CodePudding user response：

You should only group by the column whose frequency you want to get.

SELECT response, COUNT(*) AS cr
FROM Prop
GROUP BY response
ORDER BY cr DESC
LIMIT 1

You shouldn't include other columns in the SELECT list, since they're different for rows with the same response.

If you want to see the rows that have that response, you can join the table with this subquery.

SELECT p.*, c.cr
FROM Prop AS p
JOIN (
    SELECT response, COUNT(*) AS cr
    FROM Prop
    GROUP BY response
    ORDER BY cr DESC
    LIMIT 1
) AS c ON p.response = c.response

CodePudding user response：

yeah, so I created a an intermediate table res which groups just the way you did it and stores the count of it, then gets the maximum and then equates which one of these tuples have a count=maximum? those tuples(excluding the count) get displayed. go through the fiddle, it shall be clear:- https://dbfiddle.uk/f9TTCN9X

AND I have shown all those tuples which have the highest frequency... I think that's what you meant... Please clarify your question if this was not the intended answer.

My 1st query creates the intermediate table res:-

create table res(pid int,propname varchar(255),address varchar(255),city varchar(255),response varchar(255),cr int) ;
INSERT INTO res select pid, propName, Address, City, Response, count(Response) as cr
from Prop
group by 
        pid,
        propName,
        Address,
        City,
        Response;

My 2nd query does the job:-

select pid,propname,address,city,response from res where cr=(select max(cr) from res);