CODEWITHSUNDEEP
mysqlsqlsubquery
lolalola
lolalola

Reputation: 3823

MySQL: sum values from subqueries

Is it possible to sum two values from subqueries?

I need select three values: total_view, total_comments and rating.

Both subqueries is very complicated, so i don't wish duplicate it.

My query example:

SELECT p.id,
(
    FIRST subquery  
) AS total_view,
(
    SECOND subquery 
) AS total_comments,
(
    total_view * total_comments
) AS rating
FROM products p
WHERE p.status = "1"
ORDER BY rating DESC

Upvotes: 2

Views: 3011

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I would suggest using a subquery:

SELECT p.*, (total_view * total_comments) as rating
FROM (SELECT p.id,
             (FIRST subquery) AS total_view,
             (SECOND subquery) AS total_comments,
      FROM products p
      WHERE p.status = '1'  -- if status is a number, then remove quotes
     ) p
ORDER BY rating DESC;

MySQL materializes the subquery. But because the ORDER BY is on a computed column, it needs to sort the data anyway, so the materialization is not extra overhead.

Upvotes: 5

dnoeth
dnoeth

Reputation: 60472

Simply use a Derived Table to be able to reuse the aliases:

SELECT p.id,
   total_view,
   total_comments,
   total_view * total_comments AS rating
FROM
 (
   SELECT p.id,
    (
        FIRST subquery  
    ) AS total_view,
    (
        SECOND subquery 
    ) AS total_comments
    FROM products p
    WHERE p.status = "1"
 ) as dt
ORDER BY rating DESC

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

You can't use alias but you can use the same code eg:

  SELECT p.id,
  (
      FIRST subquery  
  ) AS total_view,
  (
      SECOND subquery 
  ) AS total_comments,
  (
      (
      FIRST subquery  
    ) * (
      SECOND subquery 
    )
  ) AS rating 

  FROM products p
  WHERE p.status = "1"
  ORDER BY rating DESC

Upvotes: 1

Related Questions