sql - Case statement combining two separate fields into one new field

Question

EDIT: Let me try again. There are two main tables in use here. One is called General, which I need to use to join to another table called Customer. Customer has COLUMNS, such as first name, last name, age, etc. There is only 1 record for each query, but that record may contain two customers. I want to group the new CASE column, with the ages of both c1 and c2 in the proper grouping.

So my problem below is, I can use CASE to create a new column (great), for each customer if I want to individually, but I need to GROUP BY only one new CASE, because I want the groupings to not include say a customer2 who is a 40 year age in a 20-30 grouping, which happens if customer 1 is in that grouping. Does this make sense? I don't see how this is possible without somehow creating a new table, but I don't want to modify the DB at all. Sorry if this doesn't make sense, but I inherited this and trying to solve this has been driving me crazy. The only way I can do this now is excel and moving the values manually and then using a pivot table. Here is the crux of the code: SELECT gn.general, c1.custage1 AS 'Customer1 Age', c2.custage2 AS 'Customer 2 Age', CASE WHEN c1.custage BETWEEN 20-30 THEN '20-30' WHEN c2.custage BETWEEN 20-30 THEN '20-30' END AS 'AGEGROUP' FROM GENERAL gn LEFT JOIN CUSTOMER ON c1.general = g.general AND c1.general = "CUST1" LEFT JOIN CUSTOMER ON c2.custage = g.general AND c2.general = "CUST2"

Then in my table I added a parent group, grouping by AGEGROUP, which looks great, but doesn't break up the c2.custage into it's correct category. I know the logic is flawed, just can't think of how to resolve this. Still learning obviously. Thanks for any feedback in advance.

I have 2 customers on one sale, one has c.custage1 and one has custage2. Trying to group both ages into one new column and show totals, so for example:

CASE 
    WHEN c.custage1 BETWEEN 20-30 THEN '20-30' 
    WHEN c.custage2 BETWEEN 20-30 THEN '20-30' 
END AS 'AGEGROUP' 

But this isn't working. I'd like to give group summaries of both customer ages as one. I'd prefer to not add any new columns to the tables. Thanks!

question from:https://stackoverflow.com/questions/65945366/case-statement-combining-two-separate-fields-into-one-new-field

Answer 1

Please try to spell out the word 'and'

CASE 
    WHEN c.custage1 BETWEEN 20 and 30 THEN '20-30' 
    WHEN c.custage2 BETWEEN 20 and 30 THEN '20-30' 
END AS AGEGROUP