SQL How to combine MAX and COUNT using inner query properly

Question

I'm having a problem with selecting rows only with maximum values from column ProblemsAmount, which represents COUNT(*) from inner query. It looks like:

PersonID   | PersonName   | ProblemID   | ProblemsAmount
1          | Johny        | 1           | 10
1          | Johny        | 2           | 5
1          | Johny        | 3           | 18
2          | Sara         | 4           | 2
2          | Sara         | 5           | 12
3          | Katerina     | 6           | 17
3          | Katerina     | 7           | 2
4          | Elon         | 8           | 20
5          | Willy        | 9           | 6
5          | Willy        | 10          | 2

What I want to get:

PersonID   | PersonName   | ProblemID   | ProblemsAmount
1          | Johny        | 3           | 18
2          | Sara         | 5           | 12
3          | Katerina     | 6           | 17
4          | Elon         | 8           | 20
5          | Willy        | 9           | 6

The code I have right now:

SELECT A.PersonID,
       A.PersonName,
       A.ProblemID,
       MAX(A.ProblemsCounter) AS ProblemsAmount
FROM (SELECT Person.PersonId AS PersonID,
             Person.Name AS PersonName,
             Problem.ProblemId AS ProblemID,
             COUNT(*)      AS ProblemsCounter
      FROM Person,
           Problem
      WHERE Problem.ProblemId = Person.ProblemId
      GROUP BY Person.PersonId, Person.Name, Problem.ProblemId
     ) A
GROUP BY A.PersonID, A.PersonName, A.ProblemID
ORDER BY A.PersonName, ProblemsAmount DESC;

Inner query returns the same thing as outer does, I'm confused with MAX function. It doesn't work and I don't understand why. I tried to fix it using HAVING, but it wasn't successfully.

Thanks in advance.

Gordon Linoff · Accepted Answer · 2019-03-02T18:26:42.763

A simple method that doesn't require a subquery is TOP (1) WITH TIES and ROW_NUMBER():

SELECT TOP (1) WITH TIES p.PersonId, p.Name AS PersonName,
       pr.ProblemId, COUNT(*) AS ProblemsCounter
FROM Person p JOIN
     Problem pr
     ON pr.ProblemId = p.ProblemId
GROUP BY p.PersonId, p.Name, pr.ProblemId
ORDER BY ROW_NUMBER() OVER (PARTITION BY p.PersonId ORDER BY COUNT(*) DESC);

Note that I have also fixed the JOIN syntax. Always use proper, explicit, standard JOIN syntax.

score 1 · Answer 2 · answered Mar 02 '19 at 18:01

1

no need subquery try like below and avoid coma separated join

 SELECT          Person.PersonID , 
                 Person.Name AS PersonName,
                 COUNT(Problem.ProblemId)  AS ProblemsCounter
                 ,max(ProblemsAmount) as ProblemsAmount
          FROM Person left join
               Problem
          on Problem.ProblemId = Person.ProblemId
          GROUP BY Person.PersonID, Person.Name

answered Mar 02 '19 at 18:01

Zaynul Abadin Tuhin

31,407
5
33
63

Where will I get ProblemsAmount? It seems to be not defined in your query. – Daniil Pastukhov Mar 02 '19 at 18:16
@DaniilPastukhov from max(ProblemsAmount) you will ger problemsamount – Zaynul Abadin Tuhin Mar 02 '19 at 19:03

SQL How to combine MAX and COUNT using inner query properly

2 Answers2