1

I am trying to get the STDEV of MCW_NM column but I want it to be STDEV of all rows not per group by BLADEID. But in Variance_Blade_MCW I need it to be grouped by BLADEID. I have tried over() but I get this error:

Column 'ENG.DBO.MCW_BCL_WEDGE.MCW_NM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can anyone help me? Below is my query.

PS: I am having difficulty explaining the problem so please bear with me. Let me know if you have clarifications! thanks a lot!

SELECT 
    BladeID,
    Total_Sigma_MCW = STDEV(MCW_NM) OVER (), 
    CountD_Blade = COUNT(BLADEID) OVER (), 
    Variance_Blade_MCW = SQUARE(STDEV(MCW_NM))
FROM 
    ENG.DBO.MCW_BCL_WEDGE 
WHERE 
    TESTDATE > GETDATE() - 6
GROUP BY 
    BLADEID 
HAVING
    COUNT(BladeID) >= 5000
Jervs
  • 344
  • 1
  • 12

1 Answers1

1

I don't have access to mssql at the moment, but this might work. The inner query returns 1 row per BladeID with what I think are the aggregates you want. Problem is window functions always return 1 row for each row in the source, so the outer query flattens this.

SELECT DISTINCT
    BladeID,
    Total_Sigma_MCW = STDEV(MCW_NM) OVER (PARTITION BY 1),
    Variance_Blade_MCW, 
    CountD_Blade,
FROM
(
SELECT 
    BladeID,
    MCW_NM, 
    CountD_Blade = COUNT() OVER (PARTITION BY BladeID), 
    Variance_Blade_MCW = SQUARE(STDEV(MCW_NM) OVER (PARTITION BY BLADEID))
FROM 
    ENG.DBO.MCW_BCL_WEDGE 
WHERE 
    TESTDATE > GETDATE() - 6
) q
WHERE CountD_Blade >= 5000

It may be more efficient to create two queries, one to group by BladeID and one over the full dataset and join them.

David Waterworth
  • 2,214
  • 1
  • 21
  • 41
  • Hi David, I got this error in Variance_Blade_MCW in which i needed it to be partition by bladeid. The function 'SQUARE' is not a valid windowing function, and cannot be used with the OVER clause. – Jervs May 09 '19 at 05:25
  • Also I don't need Total_Sigma_MCW to be partition by BladeID. I want it to be over the full dataset. – Jervs May 09 '19 at 05:27
  • I messed up the Variance_Blade_MCW, the PARTITION BY should have been inside the SQUARE(). Also I changed Total_Sigma_MCW to partition by 1 (i.e. same value for every row in the source table. Fingers crossed it works – David Waterworth May 09 '19 at 22:38
  • I also messed up explaining the problem. I only need the stdev of those bladeid with count> 5000. So putting CNT >=5000 is incorrect – Jervs May 10 '19 at 05:43
  • Isn't that what it's doing - although I just noticed my count isn't required as you're already doing it. – David Waterworth May 10 '19 at 05:49
  • I also had a rogue group by on the outer query, and I think you wanted std() over all rows but var() over bladeid? – David Waterworth May 10 '19 at 05:56
  • 1
    Yes David. That's exactly what I want. And I mean in the cnt >= 5000, we can't put that in the outer query as it filter only after we've computed the STDEV. We need to compute STDEV of those cnt >= 5000 – Jervs May 14 '19 at 02:14
  • I've moved the STDEV outside the inner query - I think it'll still work – David Waterworth May 14 '19 at 03:40