I would like to get a unique based on email address (MySQL)

Question

I can work on basic queries and have been having a bit of trouble the way Wordpress stores their database information. The below query I have been using to download all form submissions for contests that we run. I usually download the results and then sort what I need to using excel or google sheets. Our last bunch of contests have had a lot of submissions, and I would like to take the following query and take it one step further :

SELECT b.id, b.date_created, a.lead_id, a.form_id,
   max(case when a.field_number = 13 then value end) as FirstName,
   max(case when a.field_number = 14 then value end) as LastName,
   max(case when a.field_number = 15 then value end) as Address,
   max(case when a.field_number = 16 then value end) as Address2 ,
   max(case when a.field_number = 17 then value end) as City,
   max(case when a.field_number = 18 then value end) as Province,
   max(case when a.field_number = 19 then value end) as PostalCode,
   max(case when a.field_number = 20 then value end) as Country,
   max(case when a.field_number = 4 then value end) as PhoneNumber,
   max(case when a.field_number = 3 then value end) as EmailAddress,
   max(case when a.field_number = 44 then value end) as Question,
   max(case when a.field_number LIKE 42.1 then value end) as ClientNewsletter,
   max(case when a.field_number LIKE 27.1 then value end) as LocalNewsletter
FROM wp_rg_lead_detail a
LEFT JOIN wp_rg_lead b
ON a.lead_id = b.id
WHERE a.form_id = 45
GROUP BY a.lead_id;

This gets me my full list of entries. I would like to be able to generate three more results.

This current result I would like to get unique entries by EmailAddress

The two new lists I would like are based on whether ClientNewsletter is selected, as well as LocalNewsletter.

Any help is appreciated.

Answer 1

Are you saying just all the unique email addresses? Have you tried incorporating DISTINCT in any way?

As for the others, seems like an WHERE statement would be the way to go.

Answer 2

SELECT b.id, b.date_created, a.lead_id, a.form_id,
   max(case when a.field_number = 13 then value end) as FirstName,
   max(case when a.field_number = 14 then value end) as LastName,
   max(case when a.field_number = 15 then value end) as Address,
   max(case when a.field_number = 16 then value end) as Address2 ,
   max(case when a.field_number = 17 then value end) as City,
   max(case when a.field_number = 18 then value end) as Province,
   max(case when a.field_number = 19 then value end) as PostalCode,
   max(case when a.field_number = 20 then value end) as Country,
   max(case when a.field_number = 4 then value end) as PhoneNumber,
   max(case when a.field_number = 3 then value end) as EmailAddress,
   max(case when a.field_number = 44 then value end) as Question,
   max(case when a.field_number LIKE 42.1 then value end) as ClientNewsletter,
   max(case when a.field_number LIKE 27.1 then value end) as LocalNewsletter
FROM wp_rg_lead_detail a
LEFT JOIN wp_rg_lead b
ON a.lead_id = b.id
WHERE a.form_id = 45
GROUP BY a.lead_id;

Rather than doing the whole

max(case when a.field_number = 3 then value end) as EmailAddress

I would suggest using group_concat and not losing duplicate values that many be useful

group_concat(case when a.field_number = 3 then value end) as EmailAddress

This will return all values comma-seperated as a string. In the case that you want only distinct values you can do

group_concat(DISTINCT case when a.field_number = 3 then value end) as EmailAddress