CODEWITHSUNDEEP
sql-server
mentos35
mentos35

Reputation: 23

In SQL getting the Max() of a Count() for a specific Group by

My script

SELECT ans.Questions_Id,ans.Answer_Numeric,ans.Option_Id, opt.Description, count(ans.Option_Id) as [Count]
FROM Answers ans
LEFT OUTER JOIN Questions que
  ON ans.Questions_Id = que.Id
LEFT OUTER JOIN Options opt
  ON ans.Option_Id = opt.Id
WHERE que.Survey_Id = 1
    and ans.Questions_Id = 1        
GROUP By ans.Questions_Id,ans.Answer_Numeric,ans.Option_Id, opt.Description
ORDER BY 2, 5 desc

I am trying to get the top number responses (Description) for each Answer_Numeric. The result at the moment looks like this:

| Questions_Id | Answer_Numeric | Option_Id | Description      | Count
-----------------------------------------------------------------------
| 1            | 1              | 27        | Technology       | 183
| 1            | 1              | 24        | Personal Items   | 1
| 1            | 2              | 28        | Wallet / Purse   | 174
| 1            | 2              | 24        | Personal Items   | 3
| 1            | 2              | 26        | Spiritual        | 1
| 1            | 3              | 24        | Personal Items   | 53
| 1            | 3              | 25        | Food / Fluids    | 5
| 1            | 3              | 26        | Spiritual        | 5
| 1            | 3              | 27        | Technology       | 1
| 1            | 3              | 28        | Wallet / Purse   | 1

As from the example data from above I need it to look like this:

| Questions_Id | Answer_Numeric | Option_Id | Description      | Count
-----------------------------------------------------------------------
| 1            | 1              | 27        | Technology       | 183
| 1            | 2              | 28        | Wallet / Purse   | 174
| 1            | 3              | 24        | Personal Items   | 53

I am pretty sure that I need to have a max or something in my Having clause but everything I have tried has not worked. Would really appreciate any help on this.

Upvotes: 0

Views: 64

Answers (3)

mohan111
mohan111

Reputation: 8865

we can get the same result set in different ways and I have taken sample data set you just merge your joins in this code 

declare @Table1  TABLE 
    (Id int, Answer int, OptionId int, Description varchar(14), Count int)
;

INSERT INTO @Table1
    (Id, Answer, OptionId, Description, Count)
VALUES
    (1, 1, 27, 'Technology', 183),
    (1, 1, 24, 'Personal Items', 1),
    (1, 2, 28, 'Wallet / Purse', 174),
    (1, 2, 24, 'Personal Items', 3),
    (1, 2, 26, 'Spiritual', 1),
    (1, 3, 24, 'Personal Items', 53),
    (1, 3, 25, 'Food / Fluids', 5),
    (1, 3, 26, 'Spiritual', 5),
    (1, 3, 27, 'Technology', 1),
    (1, 3, 28, 'Wallet / Purse', 1)
;

SELECT tt.Id, tt.Answer, tt.OptionId, tt.Description, tt.Count
FROM @Table1 tt
INNER JOIN
    (SELECT OptionId,  MAX(Count)OVER(PARTITION BY OptionId ORDER BY OptionId)AS RN
    FROM @Table1
    GROUP BY OptionId,count) groupedtt 
ON 
 tt.Count = groupedtt.RN
  WHERE   tt.Count <> 5
 GROUP BY tt.Id, tt.Answer, tt.OptionId, tt.Description, tt.Count

OR 

select distinct Count, Description , Id , Answer from @Table1 e where 1 = 
(select count(distinct Count ) from @Table1 where
Count >= e.Count and (Description = e.Description))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Use row_number():

SELECT *
FROM (SELECT ans.Questions_Id, ans.Answer_Numeric, ans.Option_Id, opt.Description,
             count(*) as cnt,
             row_number() over (partition by ans.Questions_Id, ans.Answer_Numeric
                                order by count(*) desc) as seqnum
      FROM Answers ans LEFT OUTER JOIN
           Questions que
           ON ans.Questions_Id = que.Id LEFT OUTER JOIN
           Options opt
           ON ans.Option_Id = opt.Id
      WHERE que.Survey_Id = 1 and ans.Questions_Id = 1        
      GROUP By ans.Questions_Id, ans.Answer_Numeric, ans.Option_Id, opt.Description
     ) t
WHERE seqnum = 1
ORDER BY 2, 5 desc;

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use ROW_NUMBER:

SELECT Questions_Id, Answer_Numeric, Option_Id, Description, [Count]
FROM (
  SELECT ans.Questions_Id,ans.Answer_Numeric,ans.Option_Id, 
         opt.Description, count(ans.Option_Id) as [Count],
         ROW_NUMBER() OVER (PARTITION BY ans.Questions_Id, ans.Answer_Numeric
                            ORDER BY count(ans.Option_Id) DESC) AS rn
  FROM Answers ans
  LEFT OUTER JOIN Questions que
    ON ans.Questions_Id = que.Id
  LEFT OUTER JOIN Options opt
    ON ans.Option_Id = opt.Id
  WHERE que.Survey_Id = 1
        and ans.Questions_Id = 1        
  GROUP By ans.Questions_Id,
           ans.Answer_Numeric,
           ans.Option_Id, 
           opt.Description) AS t
WHERE t.rn = 1
ORDER BY 2, 5 desc

Alternatively you can use RANK so as to handle ties, i.e. more than one rows per Questions_Id, Answer_Numeric partition sharing the same maximum Count number.

Upvotes: 2

Related Questions