T-SQL - Group by Style

Question

In SSMS, I have an Orders fact table which looks like this:

Orders
ID   GroupKey    OrderID    Amount    DaysKey
----------------------------------------------
1    29          29422      23.93      14
2    29          29444     -13.28     101
3    29          29453      14.28     101
4    30          30194     100.00     103
5    32          30201     101.53      93
6    32          30291      50.00     121

I have a Groups dimension which looks like this

Groups
Key    Name           StyleKey       AvgXX
-------------------------------------------
29     Blue           2              NULL
30     xyz            1              NULL
31     doesntmatter   2              NULL
32     Red            1              NULL

I have a Days dimension which looks like this: Days

Key    Day
------------ 
1      0
2      1 
3      4
...    ...
1000   999

I have a Style dimension which looks like this:

Style
Key     Style
---------------
1       3.5
2       2

What I am trying to do is populate the [AvgXX] column on the Groups table with the average Amount for each Style where the Day is greater than or equal to 100.

So for example, the Red group has a StyleKey = 1. Therefore, the AvgXX column should show me the avg(Amount) for StyleKey = 1 where Days >= 100.

Note that Days with the same Days / DaysKey should be considered the same Day (in other words, if I have 2 entries on Day 100 that are both 1, the average of the two should be 2 / 1 = 2 not 2 / 2 = 1).

What I am struggling with is more the UPDATE statement rather than a simple select statement with a few joins.

Typically, I would do a calculation like this in a visualization tool, but in this case, sadly, I cannot.

(Note: I originally asked this question yesterday, but I wrote it poorly so it wasn't clear. Hopefully this is more so).

Answer 1

I don't understand the explanation of the grouping and averaging that you want to do for this query but it sounds like you know how to do that part.

Here is some sample code of how to update the groups table after you have calculated the values you want.

     -- 1. Do your grouping query and calculate the average
     ;WITH cte AS (
        SELECT 
            GroupKey,
            AVG(Amount) as AvgAmount
        FROM 
            #Orders O 
        WHERE
            dayskey > 100
        GROUP BY 
            GroupKey
     )

     -- 2. Join the cte to the groups table and update
     UPDATE G
     SET 
         G.AvgXX = cte.AvgAmount
     FROM 
                cte
     INNER JOIN Groups G ON cte.GroupKey = G.GroupsKey