CODEWITHSUNDEEP
sqlpivot
Y2theZ
Y2theZ

Reputation: 10412

SQL query to get the value that appeared the most for each category

I have a table like this:

Category    Reply
---------+---------------+
M             1                
F             2                
M             1                
M             3             
M             1                
M             3                
F             2                
F             1                
F             2             
F             5
F             2

I'm looking for an SQL query to return the following results:

Category   Total Number    Best Reply           Number
---------+---------------+------------------+---------------+
M             5                  1                 3
F             6                  2                 4

Total number : the number of appearance of that category (I know how to get this)

Best Reply: The Reply that was chosen the most for that category

Number : The number of time the "best Reply" was chosen

Upvotes: 0

Views: 179

Answers (2)

Bert
Bert

Reputation: 82479

You don't specify your database, so I avoided using common table expressions which would make this clearer. It could still be cleaned up a bit. I did my work on SQL Server 2008.

select  rsTotalRepliesByCategory.Category, 
        TotalRepliesByCategory,
        rsCategoryReplyCount.Reply,
        rsMaxReplies.MaxReplies
from
(
    --calc total replies
    select  Category, COUNT(*) as TotalRepliesByCategory
    from CategoryReply
    group by Category
) rsTotalRepliesByCategory
INNER JOIN
(
    --calc number of replies by category and reply
    select Category, Reply, COUNT(*) as CategoryReplyCount
    from CategoryReply
    group by Category, Reply
) rsCategoryReplyCount on rsCategoryReplyCount.Category = rsTotalRepliesByCategory.Category
INNER JOIN 
(
    --calc the max replies
    select Category, MAX(CategoryReplyCount) as MaxReplies
    from
    (
        select Category, Reply, COUNT(*) as CategoryReplyCount
        from CategoryReply
        group by Category, Reply
    ) rsCategoryReplyCount2
    group by Category
) rsMaxReplies on  rsMaxReplies.Category = rsTotalRepliesByCategory.Category and rsMaxReplies.MaxReplies = rsCategoryReplyCount.CategoryReplyCount

Here is the setup I used to play around with this:

create table CategoryReply
(
    Category char(1),
    Reply int
)

insert into CategoryReply values ('M',1)
insert into CategoryReply values ('F',2)
insert into CategoryReply values ('M',1)
insert into CategoryReply values ('M',3)
insert into CategoryReply values ('M',1)
insert into CategoryReply values ('M',3)
insert into CategoryReply values ('F',2)
insert into CategoryReply values ('F',1)                
insert into CategoryReply values ('F',2)
insert into CategoryReply values ('F',5)
insert into CategoryReply values ('F',2)

And finally, the output:

Category    TotalRepliesByCategory  Reply   MaxReplies
F   6   2   4
M   5   1   3

Upvotes: 1

GregM
GregM

Reputation: 2654

SELECT    Category, TotalNumber, Row_Number() over (order by TotalNumber)
FROM(

    SELECT Category, Sum(Reply) as TotalNumber,  Count(Reply) as Number
    From Table
    Group By Category) as temp

Would be something like that

Upvotes: 0

Related Questions