Duplicate rows that belong to multiple groups before group by

Let’s say I have a table of customers and I want to group them depending on their first and last name and then get the mean age of each group.

However, my rules are not mutually exclusive. For example :

• First name begins with letter “A”
• Last name begins with letter “A”

And some customers:

id   first_name    last_name    age  
1    Ava           Adams        36
2    Alexander     Williams     44

The first customer matches both rules and the second one only matches the first rule.

The expected result for the mean age of each group should be:

• 40 for Group 1 (Ava and Alexander)
• 36 for Group 2 (Ava)

But more than that, I would really like to group the customers like below (calculating a group field that can be used for aggregation) :

Ava            …        Group 1
Ava            …        Group 2 
Alexander      …        Group 2 

How could I do that using one SQL query ?

Answer

Consider using conditional aggregation:

select
    avg(case when first_name like 'A%' then age end) avg_age_first_name_A
    avg(case when last_name  like 'A%' then age end) avg_age_last_name_A
from mytable
where first_name like 'A%' or last_name like 'A%'

---

Edit: on the other hand if you are just looking to generate two groups, one option is union all:

select t.*, 'group 1' grp from mytable t where first_name like 'A%'
union all select t.*, 'group 2' from mytable t where last_name like 'A%'