MySQL sorting distinct/unique values

user_14800183 0
Viewed 157

Decided to break my query in to bits, to help communicate what I am trying to achieve.

I have some information about a Customer and the Coupon Code they've used, in this format

Customer Coupon Code
1 FreeDel
1 FreeDel
1 FreeDel
1 1562733
1 8842939
1 847hr64
1 83jd63j
1 FreeDel
1 8eh33jr
1 AA-2637
1 AA-9837
1 Save200
1 Save200

I want to sort it so that: If a known coupon-code prefix is available, then using syntax like this:

CASE WHEN Coupon Code LIKE 'AA-%' THEN 'AA-' CASE WHEN Coupon Code IS UNIQUE THEN 'UNIQUE' CASE WHEN Coupon Code IS NOT UNIQUE THEN 'NON-UNIQUE'

And so to output

Count Customer Coupon Code Type
6 1 Non-Unique
2 1 AA-
5 1 Unique

In this ideal, the known prefixes are added first as cases, and then unknown, unrepeated Coupon Codes are labelled Unique and then repeated Coupon Codes are labelled Non-Unique.

Any help would be super appreciated! :)

Table edits

data-science mysql sql database
2 Answers

You could count them separately:

select sum(cnt), customer, 'Non-Unique'
from (
  select customer, coupon_code, count(*) as cnt
  from ccodes
  where coupon_code not like 'AA-%'
  group by customer, coupon_code
  having cnt>1
) as q
group by customer
union
select count(*), customer, 'AA-'
from ccodes
where coupon_code like 'AA-%'
group by customer
union
select sum(cnt), customer, 'Unique'
from (
  select customer, coupon_code, count(*) as cnt
  from ccodes
  where coupon_code not like 'AA-%'
  group by customer, coupon_code
  having cnt=1
) as q
group by customer

See db-fiddle

user_10521300

You can use aggregation and conditional logic:

(case when sum( code like 'AA-%' ) > 0 then 'AA-'
      when min(code) = max(code) then 'UNIQUE'
      else 'NON-UNIQUE'
 end)

You can incorporate this into an aggregation query as:

select grp, count(*)
from (select c.customer,
             (case when sum( code like 'AA-%' ) > 0 then 'AA-'
                   when min(code) = max(code) then 'UNIQUE'
                   else 'NON-UNIQUE'
              end) as grp,
             count(*)
      from c
      group by customer
     ) x
group by grp;
user_11440350
Related