# GROUP BY Week

Hi ALL,
I have been working on a query but I am not getting the expected results.

Select * from(Arg,
B_Label,
C_Label,
D_Label,
E_Label,
F_Label AS Q1_F_Label,
Date from [dbo].[vw_F2_2] WHERE B_Label in ('XYZ','IJK') and Date >'2015-12-1' ) Q1

JOIN

(SELECT F_Label AS Q2_F_Label, AVG(Arg) AS Mean, STDEV(Arg) AS Std_Dev
FROM [dbo].[vw_F2_2] where B_Label in ('XYZ','IJK') and Date >'2015-12-1' GROUP BY F_Label) Q2

ON Q1.Q1_F_Label = Q2.Q2_F_Label .

For example I have 100 C_Labels

I am getting the AVG(Arg) and STDEV(Arg) for all the 100 C_Labels.

I want to get the AVG (Arg) and STDEV(Arg) for each week , like below

``````                      Week 1    Mean

Week 2   Mean

Week 3   Mean
``````

but with my query I am getting the average and stdev for all three weeks combined.

Thank You

Looking at your subquery that calculates the mean and stdev, it looks like you need to group by week as well (unless F_Label is indicative of the week.

So your subquery should be something like this:

``````SELECT
F_Label AS Q2_F_Label ,
DATEPART(week, Date) [Week] ,
AVG(Arg) AS Mean ,
STDEV(Arg) AS Std_Dev
FROM
[dbo].[vw_F2_2]
WHERE
B_Label IN ( 'XYZ', 'IJK' )
AND Date > '2015-12-1'
GROUP BY
F_Label ,
DATEPART(week, Date)
``````

However, even that may not give you exactly what you are looking for because the data returned by the DATEPART function is dependent on DATEFIRST setting See here. There is also the ISO_WEEK that you could use, but it depends on what your definition of a week is.

Hi James,