calculate standard deviation inside pivot table

I have a pivot query that looks like below, getting the count and AVG of rows that works just fine, but not standard deviation, STD.

How may I modify the SQL below to get the STD?

SELECT mid                             as mID,
   round((x.qty_sum / x.qty_count), 5) as qtAVG,
   round(x.qty_stddev, 5)              as qtSTDDEV,
   x.qty_count                         as qtCOUNT,
   round((x.rel_sum / x.rel_count), 5) as relAVG,
   round(x.rel_stddev, 5)              as relSTDDEV,
   x.rel_count                         as relCOUNT,
FROM (SELECT mid,
         SUM(CASE WHEN (mt = "qt") THEN 1 ELSE 0 END)   as qty_count,
         SUM(CASE WHEN (mt = "qt") THEN rt ELSE 0 END)  as qty_sum,
         STD(CASE WHEN (mt = "qt") THEN rt ELSE 0 END)  as qty_stddev
         SUM(CASE WHEN (mt = "rel") THEN 1 ELSE 0 END)  as rel_count,
         SUM(CASE WHEN (mt = "rel") THEN rel ELSE 0 END) as rel_sum,
         STD(CASE WHEN (mt = "rel") THEN rel ELSE 0 END) as rel_stddev
  FROM t_r
  GROUP BY mid) x;

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I think your only problem is the ELSE 0. You simply want NULL values, because they will be ignored:

SELECT mid                                 as mID,
       round((x.qty_sum / x.qty_count), 5) as qtAVG,
       round(x.qty_stddev, 5)              as qtSTDDEV,
       x.qty_count                         as qtCOUNT,
       round((x.rel_sum / x.rel_count), 5) as relAVG,
       round(x.rel_stddev, 5)              as relSTDDEV,
       x.rel_count                         as relCOUNT,
FROM (SELECT mid,
             SUM( mt = 'qt' )   as qty_count,
             SUM(CASE WHEN mt = 'qt' THEN rt END)  as qty_sum,
             STD(CASE WHEN mt = 'qt' THEN rt END)  as qty_stddev,
             SUM( mt = 'rel' ) as rel_count,
             SUM(CASE WHEN mt = 'rel' THEN rel END) as rel_sum,
             STD(CASE WHEN mt = 'rel' THEN rel END) as rel_stddev
      FROM t_r
      GROUP BY mid
     ) x;

Note certain other changes:
  • I simplified the logic for the counts, to remove the CASE expression. This uses a MySQL extension that treats booleans as numbers with 1 for true and 0 for false.
  • I replaced the double quotes with single quotes. Single quotes are the standard delimiter for strings.
  • I removed the ELSE clauses. Aggregation functions ignore NULL values, so this should fix your problem.

Method 2

Seems like you are trying to outsmart mysql with that subquery. As far as concerns, you don’t need this additional level of complexity, just use a simple aggregated query with a WHERE clause that filters records having mt = “qt”.

SELECT 
    mid as mID,
    ROUND(AVG(rt), 5) as qtAVG,
    ROUND(STD(rt), 5) as qtSTDDEV,
    COUNT(*) as qtCOUNT,
FROM t_r
WHERE mt =  "qt"
GROUP BY mt


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x