Dynamic Aggregate functions in SELECT

  • Hi Experts

    I have a query regarding how to efficiently  do a SELECT
    I have data in the below format
    Store_grp    Store    Value1    Value2   Value1Aggr    Value2Aggr
    SG1                 S1       10           15           MIN                  MAX

    SG1                 S2     20            30           MIN                   MAX
    SG2                S3       20           30           MIN                   MAX
    SG2               S4        40           60           MIN                   MAX

    Basicaly there are Store groups(SG1,SG2) with Stores(S1-S4) belonging to that .Also there are some values associated with each store and each value has an associated aggregation type (that can be MAX,MIN,SUM or AVG)    .Based on that it shud do the Aggregation per Store Group
    Output wud be something like
    Store_grp    Store    Value1    Value2 
    SG1                            10            30
                           S1        10           15
                           S2         20          30
    SG2                             20          60
                           S3         20          30
                           S4         40          60

    Which is the best way to do it in SQL Select

    BR
    Arshad

  • Since the different types of aggregates are a known set of values (MIN, MAX, SUM, AVG) you could do a case statement, but you'd need to ensure that there was only one Value1Agg and Value2Agg per Store_grp.  I'm a bit confused by the expected output having data at the store and store group level though.  Are you trying to do something with rollups?

  • I would use a CTE that included ALL of the aggregates and then in your main query use a CASE expression to choose which of those aggregates to actually include.

    ;
    WITH store_agg AS
    (
        SELECT Store_grp, Store, MAX(Value1) AS max_val1, MIN(Value1) AS min_val1, SUM(Value1) AS sum_val1, AVG(Value1) AS avg_val1,
            MAX(Value2) AS max_val2, MIN(Value2) AS min_val2, SUM(Value2) AS sum_val2, AVG(Value2) AS avg_val2,
            Value1Aggr, Value2Aggr
        FROM Stores
        GROUP BY Store_grp, Store, Value1Aggr, Value2Aggr
    )
    SELECT Store_grp, Store,
        CASE
            WHEN Value1Aggr = 'MAX' THEN store_agg.max_val1
            WHEN Value1Aggr = 'MIN' THEN store_agg.min_val1
            WHEN Value1Aggr = 'SUM' THEN store_agg.sum_val1
            WHEN Value1Aggr = 'AVG' THEN store_agg.avg_val1
        END AS Value1,
        CASE
            WHEN Value2Aggr = 'MAX' THEN store_agg.max_val2
            WHEN Value2Aggr = 'MIN' THEN store_agg.min_val2
            WHEN Value2Aggr = 'SUM' THEN store_agg.sum_val2
            WHEN Value2Aggr = 'AVG' THEN store_agg.avg_val2
        END AS Value2
    FROM store_agg

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thanks for the answers ..

    BR
    Arshad

  • There's no need for a CTE.

    SELECT Store_grp, Store,
      CASE
       WHEN Value1Aggr = 'MAX' THEN MAX(Value1)
       WHEN Value1Aggr = 'MIN' THEN MIN(Value1)
       WHEN Value1Aggr = 'SUM' THEN SUM(Value1)
       WHEN Value1Aggr = 'AVG' THEN AVG(Value1)
      END AS Value1,
      CASE
       WHEN Value2Aggr = 'MAX' THEN MAX(Value2)
       WHEN Value2Aggr = 'MIN' THEN MIN(Value2)
       WHEN Value2Aggr = 'SUM' THEN SUM(Value2)
       WHEN Value2Aggr = 'AVG' THEN AVG(Value2)
      END AS Value2
    FROM Stores
    GROUP BY GROUPING SETS ((Store_grp, Value1Aggr, Value2Aggr), (Store, Value1Aggr, Value2Aggr));

    EDIT: Change the GROUP BY

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply