title: multiple inner joins adds extra values to count
body: <p>I am trying to include results from other tables in a <a href="https://stackoverflow.com/questions/53718429/calculate-standard-deviation-inside-pivot-table">previous</a> query using multiple joins below:</p>
  
  <pre><code>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 t.mid,
           SUM( mt = 'qt' )   as qty_count,
           SUM(CASE WHEN t_r.mt = 'qt' THEN rt END)  as qty_sum,
           STD(CASE WHEN t_r.mt = 'qt' THEN rt END)  as qty_stddev,
           SUM( t_r.mt = 'rel' ) as rel_count,
           SUM(CASE WHEN t_r.mt = 'rel' THEN rel END) as rel_sum,
           STD(CASE WHEN t_r.mt = 'rel' THEN rel END) as rel_stddev
    FROM t_r r
  right join t_m t on t.mid = r.mid
  right join m_k m on m.mid = t.mid
  right join k_d k on m.kid = k.kid
    GROUP BY t.mid
   ) x;
  </code></pre>
  
  <p>Using my query above, <code>qty_count</code> for <code>111</code> when <code>mt</code> is <code>qt</code> returns <code>6</code> instead of <code>2</code>. <code>2 * (count of 111 in table m_k)</code></p>
  
  <p>When I remove this portion of the join, I get the desired sum for <code>qtCOUNT</code> and <code>relCOUNT</code></p>
  
  <pre><code>right join m_k m on m.mid = t.mid
  right join k_d k on m.kid = k.kid
  </code></pre>
  
  <p>What am I doing wrong and how may I solve it?</p>
  
  <p>Data:</p>
  
  <p>m_k</p>
  
  <pre><code>mid  kid
  --------
  109  2
  110  2
  110  4
  111  1
  111  2
  111  3
  </code></pre>
  
  <p>k_d</p>
  
  <pre><code>kid  k_desc
  -----------
  1    desc1
  2    desc2
  3    desc3
  4    desc4
  </code></pre>
  
  <p>m_d</p>
  
  <pre><code>mid  col1   col2   col3   col4
  -------------------------------
  109  val_a  val_d  val_g  val_j
  110  val_b  val_e  val_h  val_k
  111  val_c  val_f  val_i  val_l
  </code></pre>
  
  <p>t_r</p>
  
  <pre><code>mid  rt  stamp                  mt
  ----------------------------------
  111  3   2018-12-08 01:30:31   rel
  111  4   2018-12-08 03:41:56   qt
  111  3   2018-12-08 02:29:10   qt
  110  1   2018-12-08 06:13:51   rel
  110  5   2018-12-08 11:44:39   qt
  109  1   2018-12-08 10:39:51   rel
  </code></pre>
  
  <p>Other queries that achieve the same as above is fine.</p>