CODEWITHSUNDEEP
mysqlsql
Carlos Morales
Carlos Morales

Reputation: 1149

How can I sum values from 2 queries?

Hello everybody I'm trying to get a final sum of 2 queries.

I have this sum in euros with year 2013.

Here is my query http://sqlfiddle.com/#!2/a2638/14.

 JAN    FEB      MAR    APR     MAY     JUN      JUL    AUG     SEP     OCT     NOV     DEC
4900    4900    6400    6400    6400    6400    6400    6400    2400    2400    2400    2400

I have this sum in dolars to euros with year 2013.

Here is my query http://sqlfiddle.com/#!2/a2638/15.

 JAN    FEB      MAR    APR     MAY      JUN    JUL      AUG    SEP      OCT    NOV DEC
15386   15386   20096   20096   20096   20096   20096   20096   7536    7536    7536    7536

I'm trying to get a final sum of dolars like this:

 JAN    FEB      MAR    APR     MAY      JUN    JUL      AUG    SEP      OCT    NOV DEC
20286   20286   26496   26496   26496   26496   26496   26496   9936    9936    9936    9936

Here is query explaining everything http://sqlfiddle.com/#!2/a2638/12

Please somebody can help me?

I will really appreciate help

Upvotes: 2

Views: 116

Answers (2)

bmanvelyan
bmanvelyan

Reputation: 159

try to run this:

SELECT 
SUM(if (CONCAT(@year, '-01') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance * IF (type_money = 2, @euro, 1), 0)) Jan,
SUM(if (CONCAT(@year, '-02') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance * IF (type_money = 2, @euro, 1), 0)) Feb,
SUM(if (CONCAT(@year, '-03') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance * IF (type_money = 2, @euro, 1), 0)) Mar,
SUM(if (CONCAT(@year, '-04') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance * IF (type_money = 2, @euro, 1), 0)) Apr,
SUM(if (CONCAT(@year, '-05') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance * IF (type_money = 2, @euro, 1), 0)) May,
SUM(if (CONCAT(@year, '-06') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance * IF (type_money = 2, @euro, 1), 0)) Jun,
SUM(if (CONCAT(@year, '-07') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance * IF (type_money = 2, @euro, 1), 0)) Jul,
SUM(if (CONCAT(@year, '-08') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance * IF (type_money = 2, @euro, 1), 0)) Aug,
SUM(if (CONCAT(@year, '-09') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance * IF (type_money = 2, @euro, 1), 0)) Sep,
SUM(if (CONCAT(@year, '-10') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance * IF (type_money = 2, @euro, 1), 0)) Oct,
SUM(if (CONCAT(@year, '-11') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance * IF (type_money = 2, @euro, 1), 0)) Nov,
SUM(if (CONCAT(@year, '-12') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance * IF (type_money = 2, @euro, 1), 0)) `Dec`

FROM insurances  i
INNER JOIN policies p ON p.id = i.policy_id 
WHERE (i.initial_date >= p.date_ini
AND i.final_date   <= p.date_expired) 
;

if you will have more currencies it's better to introducee 'currency table and add it to join. look at http://sqlfiddle.com/#!2/46cba/2

Upvotes: 1

simon at rcl
simon at rcl

Reputation: 7344

Cutting and pasting from SqlFiddle:

    select a.Jan * @euro + b.Jan Jan,  a.Feb * @euro + b.Feb Feb.....
    from
    (
      SELECT
      SUM(if (CONCAT(@year, '-01') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0)) Jan,
      SUM(if (CONCAT(@year, '-02') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0)) Feb,
      ....
      1 as Id

      FROM insurances  i
      INNER JOIN policies p ON p.id = i.policy_id 
      WHERE (i.initial_date >= p.date_ini
         AND i.final_date   <= p.date_expired) 
         AND type_money = 1
    ) a
    inner join
    (
      SELECT
      SUM(if (CONCAT(@year, '-01') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0) * @euro) Jan,
      SUM(if (CONCAT(@year, '-02') BETWEEN date_format(date_ini, '%Y-%m') AND date_format(date_expired, '%Y-%m'),i.net_insurance, 0) * @euro) Feb,
      ....
  1 as Id

  FROM insurances  i
  INNER JOIN policies p ON p.id = i.policy_id 
  WHERE (i.initial_date >= p.date_ini
     AND i.final_date   <= p.date_expired) 
     AND type_money = 1
) b on b.Id = a.Id

i.e. put the two queries together; add an Id field to each so they have something to join on, and add/multiply as required.

Upvotes: 1

Related Questions