Avatar of tanj1035
tanj1035
 asked on

SQL, How to do aggregation on extracting field and range band field?

hi Experts,

1) I want to extract numeric , then do aggregation on [promo code]. I.E. I want to extract numeric from [promo code] = BWL0416A or BWL0416 to 0416, then do the aggregation of [quantity'], [number of applications] based on 0416.

2) I want to add a range band on [response score], then do the aggregation of [quantity'], [number of applications] based on the range band.

The query will be similar like

select 
     newpromocode, 
     CBD,
     CASE WHEN RESPONSESCORE BETWEEN 0 AND 500 THEN '0-500'  
          WHEN RESPONSESCORE BETWEEN 500 AND 1000  THEN '500-1000' END AS RESPONSESCORE,
     SUM(CASE WHEN RESPONSESCORE BETWEEN 0 AND 500 THEN QUANTITY END ) AS QUANTITY,
     SUM(CASE WHEN RESPONSESCORE BETWEEN 500 AND 1000 THEN QUANTITY END ) AS QUANTITY,
     SUM(CASE WHEN RESPONSESCORE BETWEEN 0 AND 500 THEN numberofapplications END ) AS numberofapplications,
     SUM(CASE WHEN RESPONSESCORE BETWEEN 500 AND 1000 THEN numberofapplications END ) AS numberofapplications
FROM  
    (
      SELECT
      extractnumeric AS newpromocode,
      CBD,
     responsescore,
     sum(case when EXTRACT NUMERIC (PROMOCODE) THEN NUMERIC THEN QUANTITY END )AS QUANTITY,
     sum(case when EXTRACT NUMERIC (PROMOCODE) THEN NUMERIC THEN numberofapplications END )AS numberofapplications
     from applications
     ) A

Open in new window


For "EXTRACT NUMERIC (PROMOCODE) ......" I do not know what the extract query is suitable for this case, so just made it up. Sorry for the complication.

Please find the attachment for the original data and result table.

Thank you,
Capture.PNG
Microsoft SQL Server

Avatar of undefined
Last Comment
tanj1035

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
chaau

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
tanj1035

ASKER
Thanks for your help;.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes