Conditional Grouping in SQL

This should be easy, but I’m having trouble with it.

I have a results table that looks like this:

store_id    cust_id    spend_ind    pct
200         2          A            0.33
200         2          B            0.33
200         3          A            0.33
300         1          A            0.50
300         100        B            0.50
400         2          A            0.33
400         2          B            0.33
400         5          B            0.33

Notice that the pct column sums to 1.0 for each store. Here’s the code to create this table:

CREATE TABLE results(
    store_id int,
    cust_id int,
    spend_ind char,
    pct float
    );

INSERT INTO results VALUES(200,2,'A',0.33);
INSERT INTO results VALUES(200,2,'B',0.33);
INSERT INTO results VALUES(200,3,'A',0.33);
INSERT INTO results VALUES(300,1,'A',0.50);
INSERT INTO results VALUES(300,100,'B',0.50);
INSERT INTO results VALUES(400,2,'A',0.33);
INSERT INTO results VALUES(400,2,'B',0.33);
INSERT INTO results VALUES(400,5,'B',0.33);

I’m trying to group the results by store, then by cust_id while creating a new category A&B if the same cust_id has both A and B spend indicators for a specific store. Here’s what I’m trying to output:

store_id   spend_ind   pct
200         A&B          0.66
200         A            0.33
300         A            0.50
300         B            0.50
400         A&B          0.66
400         B            0.33

For example, customer 2 has spending patterns A and B for store 200, so we create a new category A&B that sums the pct column for any customer with spending patterns A and B.

Answer

I would phrase this as:

select
    store_id,
    cust_id,
    group_concat(spend_ind order by spend_ind separator '&') spend_ind,
    sum(pct) pct
from results
group by store_id, cust_id

You possibly want another level of aggregation to group customers by their new category:

select store_id, spend_ind, sum(pct) pct
from (
    select
        store_id,
        cust_id,
        group_concat(spend_ind order by spend_ind separator '&') spend_ind,
        sum(pct) pct
    from results
    group by store_id, cust_id
) t
group by store_id, spend_ind

Demo on DB Fiddle

store_id | spend_ind |  pct
-------: | :-------- | ---:
     200 | A&B       | 0.66
     200 | A         | 0.33
     300 | A         | 0.50
     300 | B         | 0.50
     400 | A&B       | 0.66
     400 | B         | 0.33