SQL script is not working as intended: how to use an aggregate in a virtual column to order the results?

Question

I have this:

SELECT * FROM `npt_articles` WHERE (date_discovered >= 1464534366 AND
           date_discovered <= 1464534366 AND
                      npt_site_id = 4 AND
                      (@total_social := (comments_html + fb_total  + google_plus_one + pinterest + linked_in ) + 0.0) >
                      865.0 AND
                      http_status_code = 200) ORDER BY @total_social DESC;

It works but no where near as intended.

total_social 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.

I tried this but I'm not sure if this is the way to go:

SELECT *, @total_social := (comments_html + fb_total  + google_plus_one + pinterest + linked_in ) + 0.0 FROM `npt_articles` 
WHERE (date_discovered >= 1464534366 AND
                      npt_site_id = 4 AND
                      @total_social >
                      865.0 AND
                      http_status_code = 200) ORDER BY @total_social DESC;

I think what I want is something like this:

SELECT *, ((comments_html + fb_total  + google_plus_one + pinterest + linked_in ) + 0.0) as total_social  FROM `npt_articles` WHERE (date_discovered >= 1464534366 AND
                      npt_site_id = 4 AND
                      total_social >
                      865.0 AND
                      http_status_code = 200) ORDER BY total_social DESC;

The only issue is that MySQL Workbench says total_social doesn't exist.

I've tried using a CTE like this:

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 >= 1464534366
        AND npt_site_id = 4
        AND total_social > 865.0
        AND http_status_code = 200)
ORDER BY total_social DESC;

like in "Referring to a Column Alias in a WHERE Clause" but MySQL Workbench won't accept it because it doesn't like the WITH at that position, which I know now isn't supported by MySQL.

No doubt, with sample data and desired results someone else could figure out what you actually intend. — Gordon Linoff, Jul 28 '16 at 16:16
you do NOT need a variable for this: `select ... WHERE ((comments_html+...+linked_in) > 865))` — Marc B, Jul 28 '16 at 16:31
then how would I pass it to to ORDER BY? plus It doesn't exist outside of the row query which is why I'm doing it outside of the query. I think [virtual columns](https://mariadb.com/kb/en/mariadb/virtual-computed-columns/) might be what I'm after. — Thermatix, Jul 28 '16 at 16:39
@MarcB Im guessing the OP is using the variable so as he doesn't have to re-write the line in the order by clause. Personally I would have created a variable in the select clause like he has in the second version. I'm with Gordon on this, we need some sample data (or the site the data is being pulled from, I'm guessing he's using google scholar or something), and what he expects, and what he's getting. — DaveM, Jul 28 '16 at 16:41

score 0 · Answer 1 · edited May 23 '17 at 12:22

Right, so the solution was to use the HAVING clause which I found from "How to use a temp column in the where clause", which I think is basicly a WHERE clause that happens after a SELECT.

Now my SQL looks like this:

SELECT *, 
       (comments_html + fb_total + google_plus_one + pinterest + linked_in) + 0.0 AS total_social FROM `npt_articles` WHERE (
  date_discovered >= 1464534366 
  AND 
  npt_site_id = 4 
  AND 
  http_status_code = 200 
) 
HAVING total_social > 865.0 ORDER BY total_social DESC;

Now it does the query, does the select, calculates the total_social column and then does a second query using total_social and then orders by total_social in descending order.

SQL script is not working as intended: how to use an aggregate in a virtual column to order the results?

1 Answers1