SQL subquery counting unique rows only-CodePudding

I have a booking system whereby a user adds 2 events to a cart. They then complete a single registration form where they enter name, email, num of children, and t-shirt sizes for each child. As there is only a single registration form for the 2 events T-shirt size is recorded twice per event.

I am trying to sum the number of t-shirts in the first event and exclude the t-shirt sizes in the second event.

The only field i can identify the duplicate row by is the email column in jos_eb_registrants.

There are 2 tables involved: jos_eb_field_values - records the t-shirt size jos_eb_registrants - records the id and event_id and maps them to the corresponding field values.

I have the following query The aim is to only count records where email only exists once therefore ignore rows if the email is the same) Im struggling to get the last part working:

AND `email` IN (SELECT `id`,COUNT(`email`) 
FROM `jos_eb_registrants` GROUP BY `id` HAVING COUNT(`id`)= 1)

SELECT SUM(`field_value` = '5-6')
AS 'results1' ,SUM(`field_value` = '7-8') 
AS 'results2' ,SUM(`field_value` = '9-11')
AS 'results3' ,SUM(`field_value` = '12-13')
AS 'results4' ,SUM(`field_value` = 'S (adult)')
AS 'results5' ,SUM(`field_value` = 'M (adult)')
AS 'results6' ,SUM(`field_value` = 'L (adult)')
AS 'results7' 
FROM `jos_eb_field_values` 
WHERE `registrant_id` 
IN (SELECT `id` FROM `jos_eb_registrants` 
WHERE `published` = 1 
AND `event_id` 
IN(4784,4785,4786,4787,4788,4789,4790,4791,4784,5412,5413,5414,5415,5420) )
AND `email`
IN (SELECT `id`,COUNT(`email`)
FROM `jos_eb_registrants`
GROUP BY `id`
HAVING COUNT(`id`)= 1)

Here are the 2 tables I only want to count field_value for registrant id 22434 and ignore 22435 in jos_eb_field_values table because they have the same email address in the jos_eb_registrants table.

Thanks for any pointers... For context here is the code with @UndercoverDog corrections:

$eventid = array(4784,4785,4786,4787,4788,4789,4790,4791,4784,5412,5413,5414,5415,5420);
$i = 0;
while($i < count($eventid))
{
$eventid[$i]."\n";
$sql = "SELECT SUM(field_value = '5-6')
AS 'results1' ,SUM(field_value = '7-8')
AS 'results2' ,SUM(field_value = '9-11')
AS 'results3' ,SUM(field_value = '12-13')
AS 'results4' ,SUM(field_value = 'S (adult)')
AS 'results5' ,SUM(field_value = 'M (adult)')
AS 'results6' ,SUM(field_value = 'L (adult)')
AS 'results7' 
FROM jos_eb_field_values
WHERE `registrant_id`
IN (SELECT `id`
FROM `jos_eb_registrants` 
WHERE `published` = 1
AND `event_id` 
IN ($eventid) ) 
AND email
IN (SELECT `id`,COUNT(SELECT DISTINCT `email` 
FROM `jos_eb_registrants`)) 
FROM `jos_eb_registrants` 
GROUP BY `id` 
HAVING COUNT(`id`)= 1)";

CodePudding user response：

Distinct should do the job

"...AND email IN (SELECT id,COUNT(SELECT DISTINCT email FROM jos_eb_registrants)) FROM jos_eb_registrants GROUP BY id HAVING COUNT(id)< 1)"

CodePudding user response：

Here is the solution by selecting the min id and group by email it only selects the minimum row value see https://stackoverflow.com/a/12239289/4120588 :

AS 'results1' ,SUM(field_value = '7-8') 
AS 'results2' ,SUM(field_value = '9-11') 
AS 'results3' ,SUM(field_value = '12-13') 
AS 'results4' ,SUM(field_value = 'S (adult)') 
AS 'results5' ,SUM(field_value = 'M (adult)') 
AS 'results6' ,SUM(field_value = 'L (adult)') 
AS 'results7' 
FROM jos_eb_field_values WHERE `registrant_id` 
IN (SELECT MIN(`id`) AS `id` FROM `jos_eb_registrants` 
WHERE `event_id` IN (4784,4785,4786,4787,4788,4789,4790,4791,4784,5412,5413,5414,5415,5420) GROUP BY `email`)";```

CodePudding user response：

Thanks for providing the more explicit what you have and what you are looking for. The design of the database in this context is not as optimal, but dont know if you are locked into it yet, or if this is a new system you are preparing and can be optimized.

However, that said, if a person is signing up for multiple events, you can simply get the FIRST entry / event for a given email account and just use the entries against that for the t-shirt sizes. The other way might be to just add the total t-shirts and divide by 2 since you KNOW the events are 2, and you KNOW its entered into each. But lets go with just one.

Even though the email appears twice for a given registrant, you want the FIRST event/registration id for that person.

select
        r.email,
        min( r.id ) firstRegistrationId
    from
        jos_eb_registrants r
    WHERE
            r.published = 1 
        AND r.event_id IN ( 4784, 4785, 4786, 4787, 4788, 4789, 4790,
                            4791, 4784, 5412, 5413, 5414, 5415, 5420 )
    group by
        r.email

So above will get you all people in the qualified and published events and just grab the first event regardless of how many events. Now, you can take this and join it directly to the field-values table on just the one event and get your counts per T-shirt size.

select
        SUM(field_value = '5-6') Size5to6,
        SUM(field_value = '7-8') Size7to8,
        SUM(field_value = '9-11') Size9to11,
        SUM(field_value = '12-13') Size12to13,
        SUM(field_value = 'S (adult)') AdultSmall,
        SUM(field_value = 'M (adult)') AdultMedium,
        SUM(field_value = 'L (adult)') AdultLarge 
    from
        ( select
                r.email,
                min( r.id ) firstRegistrationId
            from
                jos_eb_registrants r
            WHERE
                    r.published = 1 
                AND r.event_id IN ( 4784, 4785, 4786, 4787, 4788, 4789, 4790,
                                    4791, 4784, 5412, 5413, 5414, 5415, 5420 )
            group by
                r.email ) OneReg
            JOIN jos_eb_field_values fv
                on OneRec.firstRegistrationId = fv.registrant_id

Now you know for future questions, trying to simply describe the scenario you are facing can better help get a final resolution :) HTH