title: PostgreSQL value of COUNT multiply by a number
body: <p>I'm a Rails developer and I'm new to writing SQL script. I have <code>users</code>, <code>portfolios</code>, <code>views</code>, <code>favorites</code> and <code>endorsements</code> tables. <code>users</code> have many <code>portfolios</code> and many <code>endorsements.</code>portfolios<code>has many</code>views<code>, many</code>favorites<code>and many</code>endorsements`.</p>
  
  <p>Here is the script I wrote</p>
  
  <pre><code>top_users = User.find_by_sql(
    "SELECT users.*, 
      COUNT(portfolios.id) + 
      COUNT(views.id) + 
      COUNT(favorites.id) + 
      COUNT(case when endorsements.portfolio_id = portfolios.id AND portfolios.user_id = users.id then 1 else 0 end) +
      COUNT(case when endorsements.user_id = users.id then 1 else 0 end)
      AS total 
    FROM users 
    LEFT OUTER JOIN portfolios ON portfolios.user_id = users.id 
    LEFT OUTER JOIN views ON views.subject_id = portfolios.id AND portfolios.user_id = users.id 
    LEFT OUTER JOIN favorites ON favorites.subject_id = portfolios.id AND portfolios.user_id = users.id 
    LEFT OUTER JOIN endorsements ON endorsements.portfolio_id = portfolios.id AND portfolios.user_id = users.id OR endorsements.user_id = users.id 
    GROUP BY users.id 
    ORDER BY total DESC LIMIT 8"
  )
  </code></pre>
  
  <p><code>total</code> count is not fully what I expect because each <code>portfolio</code> is worth <code>50</code> points, <code>view</code> is <code>2</code> points, <code>favorite</code> is worth <code>10</code> points, and <code>endorsement</code> is worth <code>2</code> points.</p>
  
  <p>Let say we have 3 users</p>
  
  <pre><code>user | COUNT 1 | COUNT 2 | COUNT 3 | COUNT 4 | COUNT 5
  -------------------------------------------------------
  1     | 0       | 0       | 0       | 0       | 10
  2     | 2       | 2       | 2       | 2       | 0
  3     | 5       | 0       | 0       | 0       | 0
  </code></pre>
  
  <p>With my script, the result come in the order of <code>user 1</code>, <code>user 2</code>, then <code>users 3</code>. However base on the points system, it should come out in the order of <code>user 3</code>, <code>user 2</code> then <code>user 1</code> because <code>user 3</code> total points is <code>250</code>, <code>users 2</code> total is <code>128</code> and <code>user 1</code> is <code>20</code>, and this is the order I expect. I did tried this:</p>
  
  <pre><code>top_users = User.find_by_sql(
    "SELECT users.*, 
      COUNT(portfolios.id) * 50 + 
      COUNT(views.id) * 2 + 
      COUNT(favorites.id) * 10 + 
      COUNT(case when endorsements.portfolio_id = portfolios.id AND portfolios.user_id = users.id then 1 else 0 end) * 2 +
      COUNT(case when endorsements.user_id = users.id then 1 else 0 end) * 2
      AS total 
    FROM users 
    LEFT OUTER JOIN portfolios ON portfolios.user_id = users.id 
    LEFT OUTER JOIN views ON views.subject_id = portfolios.id AND portfolios.user_id = users.id 
    LEFT OUTER JOIN favorites ON favorites.subject_id = portfolios.id AND portfolios.user_id = users.id 
    LEFT OUTER JOIN endorsements ON endorsements.portfolio_id = portfolios.id AND portfolios.user_id = users.id OR endorsements.user_id = users.id 
    GROUP BY users.id 
    ORDER BY total DESC LIMIT 8"
  )
  </code></pre>
  
  <p>I tried the above script but does not work for me. Any thoughts or help would be much appreciated. Again, I'm very new with raw SQL script.</p>
  
  <hr>
  
  <p><strong>UPDATED</strong>
  I ended up doing this to avoid double count issue when LEFT INNTER JOIN multiple table.</p>
  
  <pre><code>    SELECT t4.id, t4.username, t4.avatar_url, p_count * 50 + ue_count * 2 + fav_count * 10 + ep_count * 2 + COUNT(vp.id) * 2 as point
      FROM (SELECT t3.id, t3.username, t3.avatar_url, p_count, ue_count, fav_count, COUNT(ep.id) as ep_count
            FROM( SELECT t2.id, t2.username, t2.avatar_url, p_count, ue_count, COUNT(fav_p.id) as fav_count
                  FROM (SELECT t1.id, t1.username, t1.avatar_url, p_count, COUNT(e.user_id) as ue_count
                        FROM (SELECT u.*, COUNT(p.user_id) as p_count
                              FROM users u
                              LEFT OUTER JOIN (SELECT user_id, id
                                              FROM portfolios) p
                                              ON u.id = p.user_id
                                              GROUP BY u.id) t1
                        LEFT OUTER JOIN (SELECT user_id
                                        FROM endorsements) e
                                        ON e.user_id = t1.id
                        GROUP BY t1.id, t1.username, t1.avatar_url, p_count ) t2
                  LEFT OUTER JOIN (SELECT p.id, p.user_id
                                  FROM portfolios p
                                  INNER JOIN favorites
                                  ON favorites.subject_id = p.id) fav_p
                  ON fav_p.user_id = t2.id
                  GROUP BY t2.id, t2.username, t2.avatar_url, p_count, ue_count) t3
            LEFT OUTER JOIN (SELECT p.id, p.user_id
                            FROM portfolios p
                            INNER JOIN endorsements
                            ON endorsements.portfolio_id = p.id) ep
            ON ep.user_id = t3.id
            GROUP BY t3.id, t3.username, t3.avatar_url, p_count, ue_count, fav_count) t4
      LEFT OUTER JOIN (SELECT p.id, p.user_id
                      FROM portfolios p
                      INNER JOIN views
                      ON views.subject_id = p.id) vp
      ON vp.user_id = t4.id
      GROUP BY t4.id, t4.username, t4.avatar_url, p_count, ue_count, fav_count, ep_count
      ORDER BY point DESC
      LIMIT 8
  </code></pre>
  
  <p>Since I'm not familiar with SQL script as I'm a very beginner. The updated code above solve my problem but I wonder how <strong>bad</strong> the performance would be if I do that. Thanks for any inputs.</p>