CODEWITHSUNDEEP
mysqlsqlselect
AlGallaf
AlGallaf

Reputation: 635

In MYSQL use Variables from SELECT statement for Calculations?

Is it possible to use the value of two column for calculating in a new column?

Query:

SELECT department.id  AS 'ID', 
   department.des AS 'Department',  
   CASE department.des 
     WHEN 'Retail Sales' THEN ( IFNULL(SUM(form.amount), 0) 
                                + (SELECT SUM(creditnote.amount) 
                                   FROM   wf_creditnote AS creditnote 
                                   WHERE  creditnote.status = 'a' 
                                          AND YEAR(creditnote.submittime) = 2013 
                                          AND MONTH(creditnote.submittime) = 11) ) 
     ELSE IFNULL(SUM(form.amount), 0) 
   END            AS 'Actual',
   budget.budget  AS 'Budget'
FROM   (wf_analysis_billcodes AS billcode 
    LEFT JOIN wf_waiving AS form 
           ON form.billcode = billcode.bill_code 
              AND form.status = 'a' 
              AND YEAR(form.sbmt) = 2013 
              AND MONTH(form.sbmt) = 11), 
   wf_analysis AS department, 
   wf_analysis_budgets AS budget 
WHERE  department.id = billcode.dept_id 
   AND department.id = budget.dept_id 
   AND budget.year = 2013 
   AND budget.month = 11 
GROUP  BY department.des 
ORDER  BY department.id ASC

Result:

+----+------------+--------+--------+
| ID | Department | Actual | Budget |
+----+------------+--------+--------+
|  1 | IT         |    327 |    200 |
|  2 | Finance    |    312 |    500 |
|  3 | Marketing  |    100 |    650 |
|  4 | HR         |    423 |    320 |
+----+------------+--------+--------+

I want to add a percentage column through the SELECT statement.

Upvotes: 0

Views: 664

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Convert the query into a subquery, and perform a calculation in a main query, in this way:

SELECT q.*,
       ((Budget-Actual)*100/Budget) AS Percent
FROM (

  ... your query ...

) AS q

Demo --> http://www.sqlfiddle.com/#!2/9b061/2

To avoid division by 0 error, a case expression migh be used:

SELECT q.*,
       CASE WHEN Bydget <> 0 
            THEN  ((Budget-Actual)*100/Budget)
            ELSE 0
       END AS Percent
FROM (

  ... your query ...

) AS q

Upvotes: 2

Related Questions