sql - Grouping ignoring blank fields -
i having hard time identify (and fix) grouping issue have following query. data has country fields blank , query ignoring them, non blank values converted rows. tried removing country grouping error "you tried execute query not include specified expression "country" part of aggregate function". query have in access:
transform count([passed courses].[course number]) select [domain].[account id], [domain].[account name], [passed courses].[learner email address], [passed courses].category, [passed courses].country, [passed courses].[earliest start_date], [passed courses].[latest end_date] [domain master] [domain] inner join (select d3.id, d3.[learner email address], d3.[course number], d3.[transcript status], d3.domain, c2.category, [passed levels].country, [passed levels].[earliest start_date], [passed levels].[latest end_date] (select [completed courses].[learner email address], [completed courses].[level], [completed courses].category, [completed courses].[completed count], [completed courses].country, [learner dates].[earliest start_date], [learner dates].[latest end_date] (select [courses taken].[learner email address], [courses taken].[level], [courses taken].category, count([courses taken].id) [completed count], [courses taken].country (select d1.id, d1.[learner email address], d1.[course number], c1.[level], c1.category, d1.[transcript status], d1.country [data] d1 inner join [courses] c1 on d1.[course number] = c1.[course number] ) [courses taken] [courses taken].[transcript status] = "completed" group [courses taken].[learner email address], [courses taken].level, [courses taken].category, d1.country ) [completed courses] inner join (select d2.[learner email address], d2.country, min(d2.start_date) [earliest start_date], max(d2.end_date) [latest end_date] [data] d2 group d2.[learner email address], d2.country ) [learner dates] on [completed courses].[learner email address] = [learner dates].[learner email address] , [completed courses].country = [learner dates].country ([courses taken].category = 'sales') , ( (([completed courses].level = 1) , ([completed courses].[completed count] >=5)) or (([completed courses].level = 2) , ([completed courses].[completed count] >=5)) or (([completed courses].level = 3) , ([completed courses].[completed count] >=1)) ) or ([courses taken].category = 'purchase') , ( (([completed courses].level = 1) , ([completed courses].[completed count] >=5)) or (([completed courses].level = 2) , ([completed courses].[completed count] >=4)) or (([completed courses].level = 3) , ([completed courses].[completed count] >=1)) ) ) [passed levels] inner join ([data] d3 inner join courses c2 on ((d3.[course number] = c2.[course number]))) on ([passed levels].level = c2.level) , ([passed levels].[learner email address] = d3.[learner email address]) , ([passed levels].category = c2.category) ) [passed courses] on [domain].[partner domain] = [passed courses].domain group [domain].[account id], [domain].[account name], [passed courses].[learner email address], [passed courses].category, [passed courses].country, [passed courses].[earliest start_date], [passed courses].[latest end_date] pivot [passed courses].[course number]; any appreciated!
what if added case statement query if country = '' return unique guid else return country column value , alias case country
you may need wrap whole query sub query group country aliased column though. isn't tested, concept idea can't see other way of grouping country , ignoring blanks within same select, or less did 2 selects , did union instead.
regards
liam
Comments
Post a Comment