Midsummer Midsomer Murder SQL Challenge and Book Competition

It's getting close to Midsummer, where in Denmark we traditionally burn witches on Saint John's Eve.

If we misspell it slightly to Midsomer, we have the Midsomer Murders series with loads of dead.

So let's have a little murderous SQL challenge for Midsummer to give you all a chance to win another signed copy of Practical Oracle SQL.

UPDATE 2020-07-01: See the winner here:
https://www.kibeha.dk/2020/07/midsummer-midsomer-sql-winner.html

Back in April I had a little book raffle using a workout on Oracle Dev Gym, which Hildo won.

This time I'll let you be creative and create SQL to answer a little challenge in order to win the book.

The basis of the challenge is data about body counts in episodes of the TV series Midsomer Murders.

Data is retrieved from https://midsomermurders.fandom.com/wiki/Midsomer_Body_Count

(To fit the challenge, I've edited the data and left out entries like 'Numerous villagers of Midsomer Oaks' so that I only have single persons.)

The murderous data

The data is in the form of XML (actually an HTML table, somewhat edited to be easily usable as XML.) 

<table>

<tr>
<td>Ep ID</td>
<td>Title</td>
<td>Murdered During Episode</td>
<td>Murdered Prior to Episode</td>
<td>Death by Suicide</td>
<td>Accidental or Natural Death</td>
<td>Attempted Murders or Suicides</td>
</tr>

<tr>
<td>0.1</td>
<td>The Killings at Badger's Drift</td>
<td>Emily Simpson|Dennis Rainbird|Iris Rainbird|Katherine Lacey</td>
<td>Bella Trace</td>
<td>Phyllis Cadell|Michael Lacey</td>
<td>Mr. Lacey|Mrs. Lacey</td>
<td></td>
</tr>

<tr>
<td>1.1</td>
<td>Written in Blood</td>
<td>Gerald Hadleigh|Max Jennings</td>
<td>Mr. Hanlon</td>
<td></td>
<td>Ralph Lyddiard|Honoria Lyddiard</td>
<td>Amy Lyddiard</td>
</tr>

{... continues for about 24K ... }

Specifics of the XML:

• A single root element called table.
• Containing many tr elements (rows) - one for each episode.
• Containing 7 td elements (columns) per tr element.
• The 7 td elements (columns) are:
• Episode ID in the format {season}.{episode}.
• Episode title.
• Names of persons murdered during the episode.
• Names of persons murdered before the episode.
• Names of persons committing successful suicides.
• Names of persons dying accidental or natural deaths.
• Names of persons victims of attempted murders or suicides.
• Where multiple persons are mentioned in one of the "Names of" columns, they are delimited by a pipe sign |.
• The first tr element (row) is a header row containing column names.

I have created a LiveSQL script:

https://livesql.oracle.com/apex/livesql/s/j7jv2j88r9sishxgns1876dhn

The script creates a table:

create table midsomer_xml (  
   id        integer primary key  
 , the_xml   clob  
);

And populates the table with a single row with id = 1 and the ~24K XML text in the the_xml column.

The murderous challenge

Your challenge, should you choose to accept it, is to create two SQL statements to give me these outputs:

• Statement 1: Statistics on body counts for the different acts per season and grand total

• Statement 2: The distinct list of persons for the different acts per season and grand total.

The details on the desired outputs I show at the end of the post.

The murderous rules

The rules for this little competition is:

• Solutions must be in the form of a script on LiveSQL.
• Solutions must contain two statements that query the midsomer_xml table (provided in my LiveSQL script above) for the row with id=1 and produce the two outputs shown at the bottom of this blog post.
• The column header (first tr element) in the XML should not be in the output, but it is allowed to hard-code which column contains what data (i.e. that the first td element contains episode ID, the second contains title, etc.) rather than dynamically query column header names.
• The SQL statements may use anything that is available in a standard LiveSQL session, including any publicly available packages that Oracle has installed on LiveSQL.
• The LiveSQL script should have visibility Unlisted and you must post the URL to the script as a comment to this blogpost - that is your entry in the competition.
  (Note: If you have trouble posting a comment here on Blogger, the reason could be that the comment system requires cross-site tracking (third-party) cookies enabled. Sorry, but I can't change that.)
• I must be able to run your LiveSQL script without getting errors, and when I run it, it should produce the outputs shown below.
• Entries must be made before Midnight UTC on Saint John's Eve Tuesday June 23rd 2020.
• Entries will be judged on correctness (producing the desired outputs) as well as what SQL features is used, the elegance and simplicity of the code, the practicability and potential performance, and how well I like the SQL.
• I'll be the sole judge and my decision is final.
• I will evaluate entries during the week following the deadline and announce a winner no later than a week after deadline.
• The prize will be a copy of Practical Oracle SQL - signed if the winner desires it.

Good luck to all who thinks creating a little murderous Midsummer SQL can be a fun pastime 😎

The murderous output details

• Statement 1: Statistics on body counts for the different acts per season and grand total

Output in LiveSQL:

SEASON	EPISODES	ACTS_MURDER	PERSONS_MURDER	ACTS_HISTORICAL	PERSONS_HISTORICAL	ACTS_SUICIDE	PERSONS_SUICIDE	ACTS_ACCIDENT	PERSONS_ACCIDENT	ACTS_ATTEMPT	PERSONS_ATTEMPT
0	1	4	4	1	1	2	2	2	2	0	0
1	4	8	8	2	2	0	0	4	4	3	3
2	4	11	11	5	5	1	1	5	5	4	4
3	4	11	11	3	3	1	1	6	6	1	1
4	5	15	15	2	2	2	2	4	4	8	8
5	5	15	15	1	1	0	0	4	4	6	6
6	5	14	14	1	1	1	1	4	4	8	7
7	7	19	19	2	2	2	2	7	7	9	9
8	8	21	21	0	0	3	3	3	3	9	7
9	8	15	15	2	2	1	1	6	6	5	5
10	8	17	17	1	1	4	4	3	3	6	6
11	7	20	20	1	1	2	2	13	13	9	9
12	7	16	16	0	0	0	0	5	5	4	4
13	8	21	21	3	3	3	3	12	12	13	10
14	8	18	18	4	4	0	0	5	5	16	11
15	6	18	18	3	3	2	2	16	16	11	11
16	5	13	13	0	0	1	1	7	7	6	6
17	4	12	12	3	3	0	0	4	4	5	5
18	6	14	14	1	1	1	1	2	2	3	3
19	6	13	13	2	2	0	0	0	0	5	5
20	6	13	13	2	2	1	1	3	3	3	3
21	4	9	9	1	1	0	0	2	2	5	5
-	126	317	317	40	40	27	27	117	117	139	120

Last line has NULL in column season and is the grand total.

ACTS_* columns are how many individual acts of murder, suicide, etc. were committed.

PERSONS_* columns are how many distinct persons were involved in these individual acts.

Notice that for the 4 acts that results in death, ACTS_* and PERSONS_* are the same.

But ACTS_ATTEMPT and PERSONS_ATTEMPT differ, since there has been multiple attempts on some of the police officers.

• Statement 2: The distinct list of persons for the different acts per season and grand total.

Output in LiveSQL:

SEASON	EPISODES	PERSONS_MURDER	PERSONS_HISTORICAL	PERSONS_SUICIDE	PERSONS_ACCIDENT	PERSONS_ATTEMPT
0	1	Dennis Rainbird, Emily Simpson, Iris Rainbird, Katherine Lacey	Bella Trace	Michael Lacey, Phyllis Cadell	Mr. Lacey, Mrs. Lacey	-
1	4	Agnes Gray, Alan Hollingsworth, Brenda Buckley, Esslyn Carmichael, Gerald Hadleigh, Guy Gamelin, Ian Craigie, Max Jennings	Gaius Quintus, Mr. Hanlon	-	Honoria Lyddiard, Ralph Lyddiard, Simone's mother, William Carter	Amy Lyddiard, Doris Winstanley, Tim Riley
2	4	Anna Santarosa, Carla Constanza, Charles Jennings, David Whitely, Dr. Ian Aycott, Hector Bridges, John Smith, Leonard Pike, Richard Bayly, Simon Fletcher, Tara Cavendish	Catherine Hamilton, Emily Beavis, Eva Hoffman, Joan Chaplin, Judith Albiston	Rev. Stephen Wentworth	Felix Bryce, Jennifer Bryce, John Lampson, Matthew Draper, Patricia Smith	David Merrill, Hector Bridges, John Merrill, Robert Cavendish
3	4	Bella Devere, Ben Gurdie, Celia Armstrong, James Tate, Marcia Tranter, Marcus Lowrie, Michael Darrow, Peter Drinkwater, Ron Pringle, Rosemary Furman, Samantha Johnstone	Jonathan Lowrie, Mrs. Foster, Simon Tranter	Grahame Tranter	David MacKillop, Madge Fielding, Michael Weston, Mrs. Mortimer, Muriel Harrap, Ruth Weston	Samantha Johnstone
4	5	Dave Cutler, Debbie Shortlands, Elspeth Inkpen-Thomas, Felicity Inkpen-Thomas, Gerald Bennett, Gregory Chambers, Kenneth Gooders, Lloyd Kirby, Mary Mohan, Owen August, Robin Wooliscroft, Simon Reason, Steve Ramsey, Suzanna Chambers, Tristan Goodfellow	Cynthia Bennett, Karl Wainwright	Evelyn Pope, Tammie, the mother of John Field	Dave Ripert, Eddie Field, Isabel Aubrey, Ronald Stokes	Augustus Deverell, Christian Aubrey, Clarice Opperman, Evelyn Pope, Julia Gooders, Naomi Inkpen, Sean O'Connell, WPC Jay Nash
5	5	Adam Keyne, Archie Bellingham, Daniel Talbot, Dudley Carew, Emma Tysoe, Ginny Sharp, Greg Tutt, James Harrington, Lady Lavinia Chetwood, Marcus Steadman, Marjorie Empson, Melissa Townsend, Raif Canning, Susan Bartlett, Victoria Bartlett	Reverend Jonathan Ebbrell	-	Gerald Empson, Neil Laxton, Reggie Barton, Sir. Walter Talbot	Frances Le Bon, Jonah Bloxham, Liam Booker, Peter Fogden, Selwyn Proctor, Sir. Anthony Talbot
6	5	Charles Edmonton, Danny Pinchel, Dr. Duncan Goff, Eddie Darwin, Frank Webster, George Hamilton, Gordon Leesmith, Isobel Hewitt, Julian Shepherd, Keith Scholey, Larry Smith, Martin Wroath, Ruth Fairfax, Tony Parish	Andy Moore	St John Smythe-Webster	Ellis Bell, Elsie Pinchel, Kenneth Hewitt, Mrs. Scholey	Colin Hawksley, DCI Tom Barnaby, DS Gavin Troy, Mallory Edmonton, Mr. Miller, Mrs. Partridge, Phil Harrison
7	7	Alex Deakin, David Heartley-Reade, Dr. Charles Rust, Dr. James Lavery, Fiona Thompson, Gareth Heldman, Jacob Stokes, Jamie Cruickshank, Jennifer Carter, Jezebel Tripp, Jim Hale, Lillian Webster, Liz Francis, Lydia Villiers, Neville Williams, Richard Rackham, Simon Mayfield, Stephen Bannerman, Steven Curtis	Eric Edwards, Roger Heldman	Claire English, Ferdy Villiers	Conor Maplin, Lettie Edwards, Michael Bannerman, Mr. Monday, Mrs. Cole, Sir Jonathan Haslett, Susan Bannerman	Agnes Waterhouse, Alan Clifford, Camilla Crofton, Cully Barnaby, Dr. John Cole, Lydia Villiers, Michael Bannerman, Miriam Heartley-Reade, Sam Callaghan
8	8	Arthur Leggott, Bruce Hartley, Dexter Lockwood, Dr. Gregory Ransom, Dr. John Osgood, Elizabeth Key, Guy Sweetman, Harvey Crane, Henry Plummer, Jack Wilmot, Joanna Craxton, John Ransom, John Whittle, Madeline Villiers, Max Ransom, Mike Spicer, Munro Hilliard, Otto Benham, Owen Swinscoe, Patrick Pennyman, Trevor Machin	-	David Key, Rosemary Key, Stanley Lockwood	Colonel Hartley, Joan Alder, Maurice Plummer	Antonia Wilmot, DCI Tom Barnaby, DS Dan Scott, Little Mal Kirby, Margaret Winstanley, Peter Craxton, Sandra Tate
9	8	Caroline Cave, Cathy Hewlett, Connor Simpson, Dr. Wellow, Frank Hopkirk, Harriet Davis, Henry York, Lady Sandra Butler, Marion Slade, Mark Castle, Martin Barrett, Mildred Danvers, Peter Cave, Rev. Anthony Gant, Sam Judd	Frances Trevelyan, P.C. Colin Armstrong	Vivienne Marwood	Bella Slade, Giles Southerly, Jean Waverley, Mr. Barrett, Mrs. Barrett, Sir Freddy Butler	Alistair Gooding, Carolyn Armitage, Jack Magwood, Mrs. Beverley, Sir John Waverley
10	8	Alan King, Dr. Alan Delaney, Eddie Marston, Eileen Carnack, Gwen Morrison, Ian King, Jack Colby, Laura Sharp, Lionel Bell, Miles King, Mimi Clifton, Nick Cheyney, Nicky Harding, Rex Masters, Simon Bright, Steve Bright, Tony Kirby	Maria Godbold	Gina Colby, Ginger Foxton, Peter Baxter, Sarah Kirkwood	Lucinda Thacker, Ralph Wood, Ted Hayward	Carol Prentice, Charles King, Dr. James Kirkwood, Ian King, Martin Spellman, Neville Hayward
11	7	Alec Grainger, Aloysius Wilmington, Colin Thomas, Eleanor Crouch, Frederick Tomlin, Guy Sandys, Henry Hammond, Hugo Cartwright, Jack Purdy, James Parkes, Jean Wildacre, Johnny Hammond, Louise Purdy, Lynton Pargeter, Marina Fellowes, Molly Thomas, Peggy Benson, Robin Lawson, Ron Wilson, Terrance Lowther	Tommy Hicks	Gilly Galsworthy, Mr. Purdy	Charlie Finleyson, Colonel Halsey, Cyrus LeVanu, Duggie Hammond, Lady Fitzroy, Libby Wilson, Lord Fitzroy, Lucy Wilmington, Michael Wilson, Mrs. Parkes, Mrs. Wilson's Sister, Peter Thomas, Tristan Balliol	Arabella Hammond, Charlotte Knight, DCI Tom Barnaby, DS Ben Jones, Ernest Balliol, Ned Fitzroy, Nesta Goodfellow, Patrick Bradley, Stanley Goodfellow
12	7	Alistair Kingslake, Anthony Prideaux, Christa Palfrey, Daniel Snape, David Roper, Dr. Sylvia Goring, Ed Monkberry, Emily Harte, Felicity Law, Geoffrey Larkin, Graham Spate, Harriet Compton, Jim Hanley, Laurence Mann, Nicky Frazer, Richard Tanner	-	-	Grandfather Crisp, Gwendoline Frazer, Harry Godbolt, Mr. Crisp, Mr. Fountain	Elizabeth Chettham, George Jeffers, Malcolm Frazer, Miles Tully
13	8	Clifford Bunting, Faye Lennox, Fergal Jenner, Frank Bishop, Geoff Rogers, Gerald Ebbs, Giles Braithwaite, Hugh Dalgleish, Iris Holman, Jack Fincher, Jeff Bowmaker, Jenny Russell, Kitty Pottinger, Leo Fincher, Luke Archbold, Maureen Stubbs, Orlando Guest, Reverend Archie Moreland, Reverend Connor Gregory, Sonia Woodley, The vicar at Badger's Drift	An unnamed gardener, Landlord's daughter, Landlord's wife	An unnamed landlord, Caroline Maria Roberts, Molly Fielding	Caleb Burbage, David Langham, Douglas Stubbs, Gerald Woodley, Magnus, Mr. Barnaby, Mr. Conway, Mrs. Peach, Noah Fincher, Sebastian's Biological Father, Sir Richard Guillaume, The patients from Saint Fidelis Hospital	DCI Tom Barnaby, DS Ben Jones, Dawn Stock, Giles Shawcross, Jude Langham, Katie Soper, Kenny Pottinger, Norman Swanscombe, Susan Fincher, Terry Stock
14	8	Alex Preston, Conrad Walker, Dave Doggy Day, Dianne Price, Dr. Markham, Father Behan, Fran Carter, Freddie Raft, Gerry Dawkins, Len Merryman, Leticia Clifford, Mary Bingham, Mother Thomas, Olive Merryman, Patrick Morgan, Peter Slim, Sgt. Trevor Gibson, Thomas Brightwell	Carolyn Lambert, Duncan Palmer, Jeremy Lambert, Max Fuller	-	Bertie Morell, Dr. Jonathan Frost, Mr. Vertue, Robin Bingham, Sir Anthony Vertue	Byron Street, Claire Powell, DCI John Barnaby, DS Ben Jones, Ezra Canning, Jo Starling, Mother Julian, Nina Morgan, Paddy Powell, Silas Trout, William Bingham
15	6	Caroline Garrett, Colin Yule, Cy Davenport, David Farmer, Debbie Moffett, Edward Stannington, Eve Lomax, George Dormer, Giles Harrison, Gregory Brantner, Helen Caxton, Jeremy Harper, Julian DeQuetteville, Keith Mulory, Kyle Gideon, Ludo DeQuetteville, Oliver Ordish, Peter Groves	Daniel Denning, Geoffrey DeQuetteville, Mrs. Mulory	Jason Winters, Phil Caxton	Angela DeQuetteville, Bentham DeQuetteville, Felicity Mary, George William Tilman, Jack Barton, Jim Caxton, Lady Elizabeth Rodney, Marion Green, Mary Dutta, Michael Harris, Miss Felton, Reverend Stannington, Ross, Rupert DeQuetteville, Tom Stanton, Vladimir Kostelov	Beatrix Ordish, DCI John Barnaby, DS Ben Jones, Diana Davenport, Finn Robson, Gagan Dutta, Grady Felton, Keith Mulory, Sasha Fleetwood, Sylvia Mountford, Ted Denning
16	5	Amy Strickland, Atticus Bradley, Bernard King, Conor Bridgeman, Eddie Rayner, Eric Calder, Ernest Bradley, Frank Dewar, Julian Calder, Martin Strickland, Nancy Dewar, Philip Hamilton, Ross Clymer	-	Ellie Wingate	Agnes Trout, Henry Darnley, Joan Rayner, Little Rose Wilton, Max Thornfield, Mr. Fergus-Johnson, Tina Bell	Armand Stone, Gavin Hopkirk, Johnny Linklater, Nick's Stepfather, Ollie Tabori, Ruth Cameron
17	4	Andrew Maplin, Annabel Latimer, Brian Grey, Cecilie Petersen, Frank Wainwright, George Summersbee, Hannah Altman, Louis Paynton, Nadia Simons, Niall Colebrooke, Suzie Colebrooke, Toby Winning	Jessica Tyler, Johnny Carver, Sir Hugo Melmoth	-	Enrico Latimer, Mark Sampson, Mr. Stowe, William Holt	Claire Asher, DCI John Barnaby, DS Charlie Nelson, Luke Altman, Tina Tyler
18	6	Aiden McCordell, Alex Dyer, Brandon Monkford, Carter Faulkner, Christopher Corby, Clara Myerscough, Dale Nevins, Felicity Ford, Greg Eddon, Harry Wyham, Lance Auden, Tony Pitt, Yasmin Ali, Zoe Dyer	Eric Tonev	Richard Melrose	Gregory Lancaster, Lydia Dryffield	Daniel Fargo, Des McCordell, Felix Lancaster
19	6	Angus Colton, Azeem Meer, Cleo Langton, Finn Thornberry, Fitz Theara, Jacob Wheeler, Leo Henderson, Milo Craven, Roderick Craven, Ronin Chow, Samantha Berry, Seb Huntington, Vernon De Harthog	Frank Lockston, The real Jacob Wheeler	-	-	Barbara Walton, DI Ben Jones, Errol Judd, Julian Lennard, Natalie Wheeler
20	6	Adam Osoba, Aisha Khalique, Ashley Denton, Barrett Lounds, Dominic Braun, Emani Taylor, Francesca Lounds, Grady Palmerston, Laurel Newman, Mahesh Sidana, Mark Adler, Serena Madison, Terry Bellini	Brother Jozef, Conrad Argo	Neville Gallagher	Curtis Ferabbee, Jane Scott, Jenny Moss	DS Jamie Winter, Sarah Barnaby, Stella Starling
21	4	Alexander Beauvoisin, Cal Ingalls, Cornelius Tetbury, Dr. Serena Lowe, Duncan Corrigan, Eric Skye, Jemima Starling, Lex Bedford, Rosa Corrigan	Nico Dearden	-	Lara Wokoma, Lola Silvermane	Ambrose Deddington, Artie Blythe, Blaise McQuinn, Cal Ingalls, Griffon Twigg
-	126	Adam Keyne, Adam Osoba, Agnes Gray, Aiden McCordell, Aisha Khalique, Alan Hollingsworth, Alan King, Alec Grainger, Alex Deakin, Alex Dyer, Alex Preston, Alexander Beauvoisin, Alistair Kingslake, Aloysius Wilmington, Amy Strickland, Andrew Maplin, Angus Colton, Anna Santarosa, Annabel Latimer, Anthony Prideaux, Archie Bellingham, Arthur Leggott, Ashley Denton, Atticus Bradley, Azeem Meer, Barrett Lounds, Bella Devere, Ben Gurdie, Bernard King, Brandon Monkford, Brenda Buckley, Brian Grey, Bruce Hartley, Cal Ingalls, Carla Constanza, Caroline Cave, Caroline Garrett, Carter Faulkner, Cathy Hewlett, Cecilie Petersen, Celia Armstrong, Charles Edmonton, Charles Jennings, Christa Palfrey, Christopher Corby, Clara Myerscough, Cleo Langton, Clifford Bunting, Colin Thomas, Colin Yule, Connor Simpson, Conor Bridgeman, Conrad Walker, Cornelius Tetbury, Cy Davenport, Dale Nevins, Daniel Snape, Daniel Talbot, Danny Pinchel, Dave Cutler, Dave Doggy Day, David Farmer, David Heartley-Reade, David Roper, David Whitely, Debbie Moffett, Debbie Shortlands, Dennis Rainbird, Dexter Lockwood, Dianne Price, Dominic Braun, Dr. Alan Delaney, Dr. Charles Rust, Dr. Duncan Goff, Dr. Gregory Ransom, Dr. Ian Aycott, Dr. James Lavery, Dr. John Osgood, Dr. Markham, Dr. Serena Lowe, Dr. Sylvia Goring, Dr. Wellow, Dudley Carew, Duncan Corrigan, Ed Monkberry, Eddie Darwin, Eddie Marston, Eddie Rayner, Edward Stannington, Eileen Carnack, Eleanor Crouch, Elizabeth Key, Elspeth Inkpen-Thomas, Emani Taylor, Emily Harte, Emily Simpson, Emma Tysoe, Eric Calder, Eric Skye, Ernest Bradley, Esslyn Carmichael, Eve Lomax, Father Behan, Faye Lennox, Felicity Ford, Felicity Inkpen-Thomas, Felicity Law, Fergal Jenner, Finn Thornberry, Fiona Thompson, Fitz Theara, Fran Carter, Francesca Lounds, Frank Bishop, Frank Dewar, Frank Hopkirk, Frank Wainwright, Frank Webster, Freddie Raft, Frederick Tomlin, Gareth Heldman, Geoff Rogers, Geoffrey Larkin, George Dormer, George Hamilton, George Summersbee, Gerald Bennett, Gerald Ebbs, Gerald Hadleigh, Gerry Dawkins, Giles Braithwaite, Giles Harrison, Ginny Sharp, Gordon Leesmith, Grady Palmerston, Graham Spate, Greg Eddon, Greg Tutt, Gregory Brantner, Gregory Chambers, Guy Gamelin, Guy Sandys, Guy Sweetman, Gwen Morrison, Hannah Altman, Harriet Compton, Harriet Davis, Harry Wyham, Harvey Crane, Hector Bridges, Helen Caxton, Henry Hammond, Henry Plummer, Henry York, Hugh Dalgleish, Hugo Cartwright, Ian Craigie, Ian King, Iris Holman, Iris Rainbird, Isobel Hewitt, Jack Colby, Jack Fincher, Jack Purdy, Jack Wilmot, Jacob Stokes, Jacob Wheeler, James Harrington, James Parkes, James Tate, Jamie Cruickshank, Jean Wildacre, Jeff Bowmaker, Jemima Starling, Jennifer Carter, Jenny Russell, Jeremy Harper, Jezebel Tripp, Jim Hale, Jim Hanley, Joanna Craxton, John Ransom, John Smith, John Whittle, Johnny Hammond, Julian Calder, Julian DeQuetteville, Julian Shepherd, Katherine Lacey, Keith Mulory, Keith Scholey, Kenneth Gooders, Kitty Pottinger, Kyle Gideon, Lady Lavinia Chetwood, Lady Sandra Butler, Lance Auden, Larry Smith, Laura Sharp, Laurel Newman, Laurence Mann, Len Merryman, Leo Fincher, Leo Henderson, Leonard Pike, Leticia Clifford, Lex Bedford, Lillian Webster, Lionel Bell, Liz Francis, Lloyd Kirby, Louis Paynton, Louise Purdy, Ludo DeQuetteville, Luke Archbold, Lydia Villiers, Lynton Pargeter, Madeline Villiers, Mahesh Sidana, Marcia Tranter, Marcus Lowrie, Marcus Steadman, Marina Fellowes, Marion Slade, Marjorie Empson, Mark Adler, Mark Castle, Martin Barrett, Martin Strickland, Martin Wroath, Mary Bingham, Mary Mohan, Maureen Stubbs, Max Jennings, Max Ransom, Melissa Townsend, Michael Darrow, Mike Spicer, Mildred Danvers, Miles King, Milo Craven, Mimi Clifton, Molly Thomas, Mother Thomas, Munro Hilliard, Nadia Simons, Nancy Dewar, Neville Williams, Niall Colebrooke, Nick Cheyney, Nicky Frazer, Nicky Harding, Olive Merryman, Oliver Ordish, Orlando Guest, Otto Benham, Owen August, Owen Swinscoe, Patrick Morgan, Patrick Pennyman, Peggy Benson, Peter Cave, Peter Drinkwater, Peter Groves, Peter Slim, Philip Hamilton, Raif Canning, Rev. Anthony Gant, Reverend Archie Moreland, Reverend Connor Gregory, Rex Masters, Richard Bayly, Richard Rackham, Richard Tanner, Robin Lawson, Robin Wooliscroft, Roderick Craven, Ron Pringle, Ron Wilson, Ronin Chow, Rosa Corrigan, Rosemary Furman, Ross Clymer, Ruth Fairfax, Sam Judd, Samantha Berry, Samantha Johnstone, Seb Huntington, Serena Madison, Sgt. Trevor Gibson, Simon Bright, Simon Fletcher, Simon Mayfield, Simon Reason, Sonia Woodley, Stephen Bannerman, Steve Bright, Steve Ramsey, Steven Curtis, Susan Bartlett, Suzanna Chambers, Suzie Colebrooke, Tara Cavendish, Terrance Lowther, Terry Bellini, The vicar at Badger's Drift, Thomas Brightwell, Toby Winning, Tony Kirby, Tony Parish, Tony Pitt, Trevor Machin, Tristan Goodfellow, Vernon De Harthog, Victoria Bartlett, Yasmin Ali, Zoe Dyer	An unnamed gardener, Andy Moore, Bella Trace, Brother Jozef, Carolyn Lambert, Catherine Hamilton, Conrad Argo, Cynthia Bennett, Daniel Denning, Duncan Palmer, Emily Beavis, Eric Edwards, Eric Tonev, Eva Hoffman, Frances Trevelyan, Frank Lockston, Gaius Quintus, Geoffrey DeQuetteville, Jeremy Lambert, Jessica Tyler, Joan Chaplin, Johnny Carver, Jonathan Lowrie, Judith Albiston, Karl Wainwright, Landlord's daughter, Landlord's wife, Maria Godbold, Max Fuller, Mr. Hanlon, Mrs. Foster, Mrs. Mulory, Nico Dearden, P.C. Colin Armstrong, Reverend Jonathan Ebbrell, Roger Heldman, Simon Tranter, Sir Hugo Melmoth, The real Jacob Wheeler, Tommy Hicks	An unnamed landlord, Caroline Maria Roberts, Claire English, David Key, Ellie Wingate, Evelyn Pope, Ferdy Villiers, Gilly Galsworthy, Gina Colby, Ginger Foxton, Grahame Tranter, Jason Winters, Michael Lacey, Molly Fielding, Mr. Purdy, Neville Gallagher, Peter Baxter, Phil Caxton, Phyllis Cadell, Rev. Stephen Wentworth, Richard Melrose, Rosemary Key, Sarah Kirkwood, St John Smythe-Webster, Stanley Lockwood, Tammie, the mother of John Field, Vivienne Marwood	Agnes Trout, Angela DeQuetteville, Bella Slade, Bentham DeQuetteville, Bertie Morell, Caleb Burbage, Charlie Finleyson, Colonel Halsey, Colonel Hartley, Conor Maplin, Curtis Ferabbee, Cyrus LeVanu, Dave Ripert, David Langham, David MacKillop, Douglas Stubbs, Dr. Jonathan Frost, Duggie Hammond, Eddie Field, Ellis Bell, Elsie Pinchel, Enrico Latimer, Felicity Mary, Felix Bryce, George William Tilman, Gerald Empson, Gerald Woodley, Giles Southerly, Grandfather Crisp, Gregory Lancaster, Gwendoline Frazer, Harry Godbolt, Henry Darnley, Honoria Lyddiard, Isabel Aubrey, Jack Barton, Jane Scott, Jean Waverley, Jennifer Bryce, Jenny Moss, Jim Caxton, Joan Alder, Joan Rayner, John Lampson, Kenneth Hewitt, Lady Elizabeth Rodney, Lady Fitzroy, Lara Wokoma, Lettie Edwards, Libby Wilson, Little Rose Wilton, Lola Silvermane, Lord Fitzroy, Lucinda Thacker, Lucy Wilmington, Lydia Dryffield, Madge Fielding, Magnus, Marion Green, Mark Sampson, Mary Dutta, Matthew Draper, Maurice Plummer, Max Thornfield, Michael Bannerman, Michael Harris, Michael Weston, Michael Wilson, Miss Felton, Mr. Barnaby, Mr. Barrett, Mr. Conway, Mr. Crisp, Mr. Fergus-Johnson, Mr. Fountain, Mr. Lacey, Mr. Monday, Mr. Stowe, Mr. Vertue, Mrs. Barrett, Mrs. Cole, Mrs. Lacey, Mrs. Mortimer, Mrs. Parkes, Mrs. Peach, Mrs. Scholey, Mrs. Wilson's Sister, Muriel Harrap, Neil Laxton, Noah Fincher, Patricia Smith, Peter Thomas, Ralph Lyddiard, Ralph Wood, Reggie Barton, Reverend Stannington, Robin Bingham, Ronald Stokes, Ross, Rupert DeQuetteville, Ruth Weston, Sebastian's Biological Father, Simone's mother, Sir Anthony Vertue, Sir Freddy Butler, Sir Jonathan Haslett, Sir Richard Guillaume, Sir. Walter Talbot, Susan Bannerman, Ted Hayward, The patients from Saint Fidelis Hospital, Tina Bell, Tom Stanton, Tristan Balliol, Vladimir Kostelov, William Carter, William Holt	Agnes Waterhouse, Alan Clifford, Alistair Gooding, Ambrose Deddington, Amy Lyddiard, Antonia Wilmot, Arabella Hammond, Armand Stone, Artie Blythe, Augustus Deverell, Barbara Walton, Beatrix Ordish, Blaise McQuinn, Byron Street, Cal Ingalls, Camilla Crofton, Carol Prentice, Carolyn Armitage, Charles King, Charlotte Knight, Christian Aubrey, Claire Asher, Claire Powell, Clarice Opperman, Colin Hawksley, Cully Barnaby, DCI John Barnaby, DCI Tom Barnaby, DI Ben Jones, DS Ben Jones, DS Charlie Nelson, DS Dan Scott, DS Gavin Troy, DS Jamie Winter, Daniel Fargo, David Merrill, Dawn Stock, Des McCordell, Diana Davenport, Doris Winstanley, Dr. James Kirkwood, Dr. John Cole, Elizabeth Chettham, Ernest Balliol, Errol Judd, Evelyn Pope, Ezra Canning, Felix Lancaster, Finn Robson, Frances Le Bon, Gagan Dutta, Gavin Hopkirk, George Jeffers, Giles Shawcross, Grady Felton, Griffon Twigg, Hector Bridges, Ian King, Jack Magwood, Jo Starling, John Merrill, Johnny Linklater, Jonah Bloxham, Jude Langham, Julia Gooders, Julian Lennard, Katie Soper, Keith Mulory, Kenny Pottinger, Liam Booker, Little Mal Kirby, Luke Altman, Lydia Villiers, Malcolm Frazer, Mallory Edmonton, Margaret Winstanley, Martin Spellman, Michael Bannerman, Miles Tully, Miriam Heartley-Reade, Mother Julian, Mr. Miller, Mrs. Beverley, Mrs. Partridge, Naomi Inkpen, Natalie Wheeler, Ned Fitzroy, Nesta Goodfellow, Neville Hayward, Nick's Stepfather, Nina Morgan, Norman Swanscombe, Ollie Tabori, Paddy Powell, Patrick Bradley, Peter Craxton, Peter Fogden, Phil Harrison, Robert Cavendish, Ruth Cameron, Sam Callaghan, Samantha Johnstone, Sandra Tate, Sarah Barnaby, Sasha Fleetwood, Sean O'Connell, Selwyn Proctor, Silas Trout, Sir John Waverley, Sir. Anthony Talbot, Stanley Goodfellow, Stella Starling, Susan Fincher, Sylvia Mountford, Ted Denning, Terry Stock, Tim Riley, Tina Tyler, WPC Jay Nash, William Bingham

Last line has NULL in column season and is the grand total.

Each list of names is ordered alphabetically and delimited by comma.

Each list is distinct, so that in for example persons_attempt in the grand total DS Tom Barnaby does not appear multiple times.