CODEWITHSUNDEEP
mysqlsql
user14461410
user14461410

Reputation: 31

Case based on properties of group by MySQL

I have a table of orders, where each product a customer orders is on a different line.

Products are gender specific and I would like to be able to segment my customers on gender. However, some customers order both Male and Female products and I would like to be able to detect that as well.

This is a simple representation of the table structure:

+----------+----------------+----------------+
| Order ID | Customer Email | Product Gender |
+----------+----------------+----------------+
|        1 | john@          | Male           |
|        2 | john@          | Unisex         |
|        3 | matt@          | Male           |
|        4 | matt@          | Female         |
|        5 | abby@          | Female         |
+----------+----------------+----------------+

The result I am looking for would be:

john@ - Male (male+unisex would revert to male)

matt@ - Unisex (M+F / Unisex would revert to Unisex)

abby@ - Female

So I guess a case checking genders within the customer email group by, I just am not sure how to fully write it.

Upvotes: 0

Views: 49

Answers (1)

jarlh
jarlh

Reputation: 44766

Do a GROUP BY. If there are more than 1 distinct non-unisex gender, return Unisex. Otherwise return MIN(gender), which will be the only one (or the one combined with unisex.)

select Customer_Email,
       case when count(distinct case when Product_Gender <> 'Unisex'
                                     then Product_Gender
                                end) > 1 then 'unisex'
            else MIN(Product_Gender)
       end
from tablename
group by Customer_Email

Upvotes: 2

Related Questions