title: SQL script is not working as intended: how to use an aggregate in a virtual column to order the results?
body: <p>I have this:</p>
  
  <pre><code>SELECT * FROM `npt_articles` WHERE (date_discovered &gt;= 1464534366 AND
             date_discovered &lt;= 1464534366 AND
                        npt_site_id = 4 AND
                        (@total_social := (comments_html + fb_total  + google_plus_one + pinterest + linked_in ) + 0.0) &gt;
                        865.0 AND
                        http_status_code = 200) ORDER BY @total_social DESC;
  </code></pre>
  
  <p>It works but no where near as intended.</p>
  
  <p><code>total_social</code> is the aggregate value of other named fields of the same row (record, object, etc.) which is then compared against a number  which is injected into the query string. I want to also use this aggregate in a virtual column to then be used to order the results. </p>
  
  <p>I tried this but I'm not sure if this is the way to go:</p>
  
  <pre><code>SELECT *, @total_social := (comments_html + fb_total  + google_plus_one + pinterest + linked_in ) + 0.0 FROM `npt_articles` 
  WHERE (date_discovered &gt;= 1464534366 AND
                        npt_site_id = 4 AND
                        @total_social &gt;
                        865.0 AND
                        http_status_code = 200) ORDER BY @total_social DESC;
  </code></pre>
  
  <p>I think what I want is something like this:</p>
  
  <pre><code>SELECT *, ((comments_html + fb_total  + google_plus_one + pinterest + linked_in ) + 0.0) as total_social  FROM `npt_articles` WHERE (date_discovered &gt;= 1464534366 AND
                        npt_site_id = 4 AND
                        total_social &gt;
                        865.0 AND
                        http_status_code = 200) ORDER BY total_social DESC; 
  </code></pre>
  
  <p>The only issue is that MySQL Workbench says <code>total_social</code> doesn't exist.</p>
  
  <p>I've tried using a CTE like this:</p>
  
  <pre><code>WITH inner_table AS (
      SELECT 
          ((comments_html + fb_total + google_plus_one + pinterest + linked_in) + 0.0) AS total_social
      FROM
          `npt_articles`
  )
  select * FROM inner_table
  WHERE
      (date_discovered &gt;= 1464534366
          AND npt_site_id = 4
          AND total_social &gt; 865.0
          AND http_status_code = 200)
  ORDER BY total_social DESC;   
  </code></pre>
  
  <p>like in "<a href="https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause/8370216#8370216">Referring to a Column Alias in a WHERE Clause</a>" but MySQL Workbench won't accept it because it doesn't like the <code>WITH</code> at that position, which I know now isn't supported by MySQL.</p>