I have the following table:

I have the following SQL code which isn't working:

SELECT MAX(SUM(case when Type = 'email' then 1 else 0 end)) AS 'MaxEmails'
FROM Tickets AS T
INNER JOIN Officers AS O
  ON T.OfficerID = O.OfficerID
INNER JOIN Types AS Ty
  ON T.TypeID = Ty.TypeID
INNER JOIN Actions AS A
  ON T.ActionID = A.ActionID
INNER JOIN ControlledParkingZones AS C
  ON T.CPZCode = C.CPZCode;

I am trying to return the name of the CPZName with the Maximum number of Emails.

I'd like the results to look something like this:

CodePudding user response：

I am trying to return the name of the CPZName with the Maximum number of Emails.

SELECT TOP 1 CPZName, COUNT(*) AS NumEmails
FROM [MyData]
WHERE [Type]='email'
GROUP BY CPZName
ORDER BY COUNT(*) DESC

I'd like the results to look something like this...

For multiple items like this in the same result row, you run a separate query similar to the above as a nested/subquery for each column:

SELECT 
   (
       SELECT TOP 1 CPZName
       FROM [MyData]
       WHERE [Type]='email'
       GROUP BY CPZName
       ORDER BY COUNT(*) DESC
    ) As MaxEmail, 
   (
       SELECT TOP 1 CPZName
       FROM [MyData]
       WHERE [Type]='email'
       GROUP BY CPZName
       ORDER BY COUNT(*)
    ) As MinEmail, 
 ...

CodePudding user response：

You can do it in two steps.

First, aggregate and order the results, then pick the values you're searching for.

WITH
  CPZName_Summary AS
(
  SELECT
    CPZName,
    Type,
    ROW_NUMBER() OVER (
      PARTITION BY CPZName, Type
          ORDER BY COUNT(*)
    )
      AS row_asc,
    ROW_NUMBER() OVER (
      PARTITION BY CPZName, Type
          ORDER BY COUNT(*) DESC
    )
      AS row_desc
  FROM
    [yourData]
  GROUP BY
    CPZName,
    Type
)
SELECT
  MAX(CASE WHEN Type = 'email'  AND row_asc  = 1 THEN CPZName END)  AS MinEmail,
  MAX(CASE WHEN Type = 'email'  AND row_desc = 1 THEN CPZName END)  AS MaxEmail,
  MAX(CASE WHEN Type = 'letter' AND row_asc  = 1 THEN CPZName END)  AS MinLetter,
  MAX(CASE WHEN Type = 'letter' AND row_desc = 1 THEN CPZName END)  AS MaxLetter
FROM
  CPZName_Summary
WHERE
  Type IN ('email', 'letter')

Take care for when multiple CPZName's are tied with the same number of emails. You may want to add something to the ORDER BY clauses to guarantee you get the one you want, or rethink the output structure so you can return multiple values in the event of ties.