February 28, 2017 at 10:21 am
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
February 28, 2017 at 1:26 pm
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?
February 28, 2017 at 3:03 pm
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
March 20, 2017 at 10:07 am
Thanks for the answers ..
BR
Arshad
March 20, 2017 at 11:50 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply