Monday, 25 August 2014

How to use value from row derived column from a table in another query?

SELECT...INTO can be used to store the single row column result value from a query to be used in another query depending on that value.


"accounts" table

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| account_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| account_name | varchar(50) | NO   |     | NULL    |                |
| balance      | float       | NO   |     | NULL    |                |
| state        | varchar(50) | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

Sample data

+------------+--------------+---------+-------+
| account_id | account_name | balance | state |
+------------+--------------+---------+-------+
|          1 | Rico         |     500 | IL    |
|          2 | Mark         |     100 | NY    |
|          3 | Rita         |      50 | NY    |
+------------+--------------+---------+-------+

Lets say we want to get average balance of all accounts in the state of NY from the above table and then use that value in another query.

mysql> SELECT AVG(balance) INTO @avg_bal
    -> FROM accounts
    -> WHERE state = 'NY'
    -> GROUP BY state;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('Average balance in NY is ', @avg_bal) AS result;
+-----------------------------+
| result                      |
+-----------------------------+
| Average balance in NY is 75 |
+-----------------------------+
1 row in set (0.00 sec)

The above use case arises when we have 2 or more queries depending on each others' output inside a stored procedure.

Below is how the stored procedure would look:

DELIMITER $$

DROP PROCEDURE IF EXISTS get_ny_avg_bal$$

CREATE PROCEDURE get_ny_avg_bal()
BEGIN
    DECLARE avg_bal FLOAT;

    SELECT AVG(balance) INTO avg_bal
    FROM accounts
    WHERE state = 'NY'
    GROUP BY state;

    SELECT CONCAT('Average balance in NY is ', avg_bal) AS result;

END$$

DELIMITER ;

mysql> CALL get_ny_avg_bal();
+-----------------------------+
| result                      |
+-----------------------------+
| Average balance in NY is 75 |
+-----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

1 comment:

  1. Your table definition hints that you are storing a monetary value as a FLOAT.

    Don't do that! Monetary values should always be stored a DECIMAL.

    ReplyDelete