I have the following table: TicketNumber FullName Type ActionTaken CPZName DateReceived DateResponded A306250992 Robson de Souza letter Re-issue Notice Lannisport 06/09/2022 2022/10/06 10:35:48 A306180194 Rivaldo Vitor Borba Ferreira email Re-issue Notice Lannisport NULL 2022/01/06 12:07:05 A306054145 Ronaldo de Assis Moreira User Note Hold For 14 Days Jurassic Park 06/09/2022 2022/11/06 20:53:16 A307716063 Wayne Mark Rooney email Re-Offer Discount Atlantis NULL

How can I return the Maximum Letters for a particular location using SQL?

I have the following table:

TicketNumber	FullName	Type	ActionTaken	CPZName	DateReceived	DateResponded
A306250992	Robson de Souza	letter	Re-issue Notice	Lannisport	06/09/2022	2022/10/06 10:35:48
A306180194	Rivaldo Vitor Borba Ferreira	email	Re-issue Notice	Lannisport	NULL	2022/01/06 12:07:05
A306054145	Ronaldo de Assis Moreira	User Note	Hold For 14 Days	Jurassic Park	06/09/2022	2022/11/06 20:53:16
A307716063	Wayne Mark Rooney	email	Re-Offer Discount	Atlantis	NULL	2022/08/06 10:30:04
A304495365	Joseph John Cole	email	Notice Of Rejection With Discount	Lannisport	NULL	2022/01/06 08:12:54
A306138799	Rivaldo Vitor Borba Ferreira	letter	No reply	Lannisport	NULL	2022/06/06 12:15:34
A306119841	Lionel Andrés Messi	User Note	Notice Of Rejection With Discount	Lannisport	06/10/2022	2022/10/06 13:35:12
A30621503A	Steven George Gerrard	letter	No Action	Lannisport	06/01/2022	2022/10/06 15:19:54
A307480160	Samuel Eto’o Fils	User Note	Re-Issue NTO HHT	Lilliput	06/06/2022	2022/07/06 12:25:19
A306176776	Carlos Alberto Tevez	email	No Action	Lannisport	NULL	2022/06/06 09:21:09
A304337761	Rivaldo Vitor Borba Ferreira	letter	Cancel	Hogwarts	06/10/2022	2022/10/06 12:44:04
A306207610	Joseph John Cole	email	Re-issue Notice	Lannisport	NULL	2022/01/06 13:08:41
A304499402	Vincent Jean Mpoy Kompany	letter	Cancel	Lannisport	NULL	2022/06/06 10:21:00
A306102350	Leroy Aziz Sané	letter	Cancel	Lannisport	NULL	2022/01/06 10:33:52
A30763960A	Gareth Frank Bale	letter	Hold For 14 Days	Crownlands	06/02/2022	2022/09/06 14:03:03
A306160018	Rivaldo Vitor Borba Ferreira	letter	Notice Of Rejection With Discount	Lannisport	NULL	2022/01/06 14:20:43
A307657951	Ronaldo Luís Nazário de Lima	letter	Hold For 14 Days	Crownlands	06/02/2022	2022/10/06 13:27:41
A307678601	Luís Carlos Almeida de Cunha	email	Hold For 14 Days	Atlantis	NULL	2022/08/06 10:43:07
A306235387	Steven George Gerrard	letter	Notice Of Rejection With Discount	Lannisport	NULL	2022/10/06 12:55:06
A307499726	Steven George Gerrard	letter	Re-Issue NTO HHT	Hogwarts	06/08/2022	2022/09/06 12:59:52
A30614027A	Lionel Andrés Messi	email	No Action	Lannisport	NULL	2022/09/06 11:09:33
A306125072	Dimitar Ivanov Berbatov	letter	Re-issue Notice	Asgard	NULL	2022/06/06 17:09:53
A307699033	José María Gutiérrez Hernández	email	No Action	Atlantis	NULL	2022/08/06 11:53:07
A30772307A	Leroy Aziz Sané	email	Hold For 14 Days	Bankside	06/05/2022	2022/10/06 16:23:47
A306071803	Ronaldo de Assis Moreira	User Note	Hold For 14 Days	Jurassic Park	06/11/2022	2022/11/06 21:30:50
A306238716	Joseph John Cole	email	Notice Of Rejection With Discount	Lannisport	NULL	2022/10/06 10:22:14
A307703072	Diego Armando Maradona	email	Hold For 14 Days	Jurassic Park	06/07/2022	2022/10/06 12:01:30
A307704020	Luís Carlos Almeida de Cunha	email	No reply	Hogsmeade	NULL	2022/01/06 09:57:04
A307672660	Ronaldo Luís Nazário de Lima	email	Hold For 14 Days	Crownlands	06/03/2022	2022/09/06 15:34:43

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:

Answer

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, 
 ...

Advertisement

Answer