Get Summary of SQL Server Table by groups

Question

I have a table where users get to set a reaction/vote to a given event and I want to be able to create a summary view to see which reaction was voted on most times, divided by group per event.

The sample data is as follows:

DECLARE @voteTable AS TABLE (
    id              INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    eventId     VARCHAR(255) NOT NULL
,   isGroupA    INT NOT NULL
,   isGroupB    INT NOT NULL
,   userVote    VARCHAR(255) NOT NULL
);


INSERT INTO @voteTable (eventId, isGroupA, isGroupB, userVote)
VALUES
('event1','0','0','fantastic'),
('event1','0','0','fantastic'),
('event1','0','0','fantastic'),
('event1','0','0','fantastic'),
('event1','0','0','fantastic'),
('event1','0','0','fantastic'),
('event1','0','0','meh'),
('event1','0','0','meh'),
('event1','1','0','fine'),
('event1','1','0','fine'),
('event1','1','0','great'),
('event1','1','0','ok'),
('event1','1','0','ok'),
('event1','1','0','ok'),
('event1','0','1','fine'),
('event1','0','1','great'),
('event1','0','1','great'),
('event1','0','1','ok'),
('event1','1','1','bad'),
('event1','1','1','bad'),
('event1','1','1','horrible'),
('event1','1','1','horrible'),
('event1','1','1','horrible'),
('event1','1','1','horrible'),
('event1','1','1','horrible'),
('event1','1','1','ok'),
('event2','0','0','fantastic'),
('event2','0','0','fantastic'),
('event2','0','0','fantastic'),
('event2','0','0','horrible'),
('event2','0','0','fantastic'),
('event2','0','0','fantastic'),
('event2','0','0','fine'),
('event2','0','0','great'),
('event2','1','0','meh'),
('event2','1','0','meh'),
('event2','1','0','ok'),
('event2','1','0','ok'),
('event2','1','0','ok'),
('event2','1','0','ok'),
('event2','0','1','bad'),
('event2','0','1','bad'),
('event2','0','1','bad'),
('event2','0','1','bad'),
('event2','1','1','fine'),
('event2','1','1','fine'),
('event2','1','1','great'),
('event2','1','1','great'),
('event2','1','1','ok'),
('event2','1','1','bad'),
('event2','1','1','ok'),
('event2','1','1','ok')

The output I want to get should be like:

eventId |  groupA  |  groupB  | everyone
----------------------------------------
 event1 | horrible | horrible | fantastic
 event2 |    ok    |    bad   |    ok

The reason being that for event1:

• Those with a 1 for isGroupA voted "horrible" 5 times, more than any other vote type.
• Those with a 1 for isGroupB also voted "horrible" 5 times, more than any other vote.
• The most votes regardless of group for event1 was "fantastic."

Similarly for event2:

• Those in groupA voted "ok" 7 times, more than any other vote.
• Those in groupB voted "bad" 5 times, more than any other vote.
• Vote type with the highest frequency regardless of group was "ok"

I hope I was clear in my problem. Let me know if I was unclear or if I need to clarify anything.

I was thinking of doing something with a COUNT(isGroupA) as aVotes, grouping by eventId and userVote and using the RANK() function, but I can't seem to wrap my head around how to structure the whole query.

Thanks in advance for the help!

Answer 1

You are looking for the mode. One method for getting this uses aggregation and window functions:

select eventid,
       max(case when grp = 'A' then uservote end) as groupA,
       max(case when grp = 'B' then uservote end) as groupB,
       max(case when grp = 'Both' then uservote end) as both 
from (select eventid, uservote, grp, count(*) as cnt,
             row_number() over (partition by eventid, grp order by count(*) desc) as seqnum
      from votetable vt cross apply
           (values (case when isGroupA = '1' then 'A' end),
                   (case when isGroupB = '1' then 'b' end),
                   ('Both')
           ) v(grp)
      where grp is not null
      group by eventid, uservote, grp
     ) eg
where seqnum = 1
group by eventId;

Here is a SQL Fiddle.

Answer 2

using Cte table

;
with CteCount as(
Select eventId, isGroupA, null as isGroupB,
ROW_NUMBER() over (Partition by eventId, isGroupA, UserVote Order by eventId, isGroupA, UserVote) as isGroupACount,
null as isGroupBCount,
null as isAllCount,
uservote
from @voteTable
where isGroupA = 1

UNION ALL

Select eventId, null as isGroupA, isGroupB,
null,
ROW_NUMBER() over (Partition by eventId, isGroupB, UserVote Order by eventId, isGroupB, UserVote) as isGroupBCount,
null,
uservote
from @voteTable
where isGroupB = 1

UNION ALL

Select eventId, null as isGroupA, null as isGroupB, 
null,
null,
ROW_NUMBER() over (Partition by eventId, UserVote Order by eventId, UserVote) as isAllCount,
uservote
from @voteTable
),
CteSummary as(
Select eventId,
max(isGroupACount) as GroupA,
max(isGroupBCount) as GroupB,
max(isAllCount) as isAll
from CteCount
Group by eventId
)
Select
*,
(Select a.userVote from CteCount a where a.isGroupA = 1 and isGroupACount = GroupA and a.eventId = CteSummary.eventId) as GroupAvote,
(Select a.userVote from CteCount a where a.isGroupB = 1 and isGroupBCount = GroupB and a.eventId = CteSummary.eventId) as GroupBvote,
(Select a.userVote from CteCount a where a.isAllCount = isAll and a.eventId = CteSummary.eventId) as Allvote

from CteSummary