Exception: Mysql2::Error: Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'monolith_production.program_contacts.city' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by:
(
SELECT DISTINCT organizations.id, organizations.name, sort_name, organizations.type,
GROUP_CONCAT(DISTINCT(
CASE memberships.type
WHEN 'Membership::Organizational::Dual' THEN 'US/Canadian Engineering & Technology Member'
WHEN 'Membership::Organizational::Engineering' THEN 'US/Canadian Engineering Member'
WHEN 'Membership::Organizational::EngineeringTech4' THEN 'US/Canadian Technology 4 Year Member'
WHEN 'Membership::Organizational::EngineeringTech2' THEN 'US/Canadian Technology 2 Year Member'
WHEN 'Membership::Organizational::K12' THEN 'K-12 Institutional Member'
WHEN 'Membership::Organizational::CollegeAffiliate' THEN 'College Affiliate Member'
WHEN 'Membership::Organizational::NonUSCanadian' THEN 'Non US/Canadian Engineering Member'
WHEN 'Membership::Organizational::International' THEN 'International Member'
WHEN 'Membership::Organizational::InternationalLdn' THEN 'International (Least Developed Nation) Member'
WHEN 'Membership::Organizational::Government' THEN 'Government Member'
WHEN 'Membership::Organizational::Corporate' THEN 'Corporate Member'
WHEN 'Membership::Organizational::CorporateAffiliate' THEN 'Corporate Affiliate Member'
WHEN 'Membership::Organizational::Association' THEN 'Association Member'
WHEN 'Membership::Organizational::AssociationAffiliate' THEN 'Association Affiliate Member'
END
)) AS program,
IFNULL(program_contacts.city, org_contacts.city) AS 'city',
CASE WHEN states.abbreviation IS NOT NULL THEN states.abbreviation
WHEN states.name IS NOT NULL THEN states.name
WHEN program_contacts.state_other IS NOT NULL THEN program_contacts.state_other
WHEN org_contacts.state_other IS NOT NULL THEN org_contacts.state_other
END AS 'state',
IFNULL(program_contacts.website, org_contacts.website) AS 'website',
people.first_name, people.last_name,
program_contacts.department,
IFNULL(sections.name,'Non US') AS 'section',
-- cheat so hard
GROUP_CONCAT( DISTINCT
IF( all_heads.id IS NOT NULL AND all_program_contacts.department IS NOT NULL,
CONCAT( all_heads.first_name, ' ', all_heads.last_name, '<br>', all_program_contacts.department) ,
''
)
SEPARATOR '<br><br>'
) as all_heads
FROM memberships
JOIN organizations ON organizations.id = member_id AND member_type = 'Organization'
LEFT JOIN programs ON programs.id = organizations.primary_program_id
LEFT JOIN contacts program_contacts ON program_contacts.contactable_id = programs.id AND program_contacts.contactable_type = 'Program'
LEFT JOIN contacts org_contacts ON (org_contacts.contactable_id = organizations.id AND org_contacts.contactable_type = 'Organization')
LEFT JOIN states ON states.id= program_contacts.state_id OR states.id = org_contacts.state_id
LEFT JOIN people ON programs.head_id = people.id
LEFT JOIN leadership_bodies sections ON sections.id = section_id
LEFT JOIN programs all_programs ON all_programs.organization_id = organizations.id AND all_programs.type != 'OtherProgram'
LEFT JOIN people all_heads ON all_heads.id = all_programs.head_id
LEFT JOIN contacts all_program_contacts ON all_program_contacts.contactable_id = all_programs.id AND all_program_contacts.contactable_type = 'Program'
WHERE
(memberships.start_date <= NOW() AND memberships.grace_period_end_date >= NOW() AND STATUS = 'Paid')
AND (`memberships`.`type` IN ('Membership::Organizational::NonAcademic', 'Membership::Organizational::Government', 'Membership::Organizational::CorporateAffiliate', 'Membership::Organizational::AssociationAffiliate', 'Membership::Organizational::Association', 'Membership::Organizational::Corporate'))
GROUP BY organizations.id
ORDER BY organizations.name)
Exception: Mysql2::Error: Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'monolith_production.program_contacts.city' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by:
(
SELECT DISTINCT organizations.id, organizations.name, sort_name, organizations.type,
GROUP_CONCAT(DISTINCT(
CASE memberships.type
WHEN 'Membership::Organizational::Dual' THEN 'US/Canadian Engineering & Technology Member'
WHEN 'Membership::Organizational::Engineering' THEN 'US/Canadian Engineering Member'
WHEN 'Membership::Organizational::EngineeringTech4' THEN 'US/Canadian Technology 4 Year Member'
WHEN 'Membership::Organizational::EngineeringTech2' THEN 'US/Canadian Technology 2 Year Member'
WHEN 'Membership::Organizational::K12' THEN 'K-12 Institutional Member'
WHEN 'Membership::Organizational::CollegeAffiliate' THEN 'College Affiliate Member'
WHEN 'Membership::Organizational::NonUSCanadian' THEN 'Non US/Canadian Engineering Member'
WHEN 'Membership::Organizational::International' THEN 'International Member'
WHEN 'Membership::Organizational::InternationalLdn' THEN 'International (Least Developed Nation) Member'
WHEN 'Membership::Organizational::Government' THEN 'Government Member'
WHEN 'Membership::Organizational::Corporate' THEN 'Corporate Member'
WHEN 'Membership::Organizational::CorporateAffiliate' THEN 'Corporate Affiliate Member'
WHEN 'Membership::Organizational::Association' THEN 'Association Member'
WHEN 'Membership::Organizational::AssociationAffiliate' THEN 'Association Affiliate Member'
END
)) AS program,
IFNULL(program_contacts.city, org_contacts.city) AS 'city',
CASE WHEN states.abbreviation IS NOT NULL THEN states.abbreviation
WHEN states.name IS NOT NULL THEN states.name
WHEN program_contacts.state_other IS NOT NULL THEN program_contacts.state_other
WHEN org_contacts.state_other IS NOT NULL THEN org_contacts.state_other
END AS 'state',
IFNULL(program_contacts.website, org_contacts.website) AS 'website',
people.first_name, people.last_name,
program_contacts.department,
IFNULL(sections.name,'Non US') AS 'section',
-- cheat so hard
GROUP_CONCAT( DISTINCT
IF( all_heads.id IS NOT NULL AND all_program_contacts.department IS NOT NULL,
CONCAT( all_heads.first_name, ' ', all_heads.last_name, '<br>', all_program_contacts.department) ,
''
)
SEPARATOR '<br><br>'
) as all_heads
FROM memberships
JOIN organizations ON organizations.id = member_id AND member_type = 'Organization'
LEFT JOIN programs ON programs.id = organizations.primary_program_id
LEFT JOIN contacts program_contacts ON program_contacts.contactable_id = programs.id AND program_contacts.contactable_type = 'Program'
LEFT JOIN contacts org_contacts ON (org_contacts.contactable_id = organizations.id AND org_contacts.contactable_type = 'Organization')
LEFT JOIN states ON states.id= program_contacts.state_id OR states.id = org_contacts.state_id
LEFT JOIN people ON programs.head_id = people.id
LEFT JOIN leadership_bodies sections ON sections.id = section_id
LEFT JOIN programs all_programs ON all_programs.organization_id = organizations.id AND all_programs.type != 'OtherProgram'
LEFT JOIN people all_heads ON all_heads.id = all_programs.head_id
LEFT JOIN contacts all_program_contacts ON all_program_contacts.contactable_id = all_programs.id AND all_program_contacts.contactable_type = 'Program'
WHERE
(memberships.start_date <= NOW() AND memberships.grace_period_end_date >= NOW() AND STATUS = 'Paid')
AND (`memberships`.`type` IN ('Membership::Organizational::NonAcademic', 'Membership::Organizational::Government', 'Membership::Organizational::CorporateAffiliate', 'Membership::Organizational::AssociationAffiliate', 'Membership::Organizational::Association', 'Membership::Organizational::Corporate'))
GROUP BY organizations.id
ORDER BY organizations.name)