Calculate 2 different values for the past 6 months in one query

Databases
,
1

I want to SUM the total sales every month for the past 6 months and * that by the commission percentage. What I have below is working but I also want to calculate the total refunds for the month and then deduct them from the result I get from the below query. That means I would also need to run another SELECT SUM query in here but I don’t know how. The refunds/credit table is called ‘credit’ and the column for the total is ‘credit_value’ and the refund date column is ‘credit_date’.

    SELECT
      year(v.purchased),
      month(v.purchased),
      SUM(sales_price * commission) as total
  FROM
      vouchers v
      INNER JOIN commission c ON v.voucher_id = c.voucher_id
  WHERE
      v.status = 'Approved'
      AND v.purchased>=date_sub(curdate(), interval 6 month)
  GROUP BY
      year(v.purchased),
      month(v.purchased)
  ORDER BY
      year(v.purchased),
      month(v.purchased)
2

sql1 - your query

sql2 - new query of refunds with same structure

SELECT sql1.*, sql1.total - IFNULL(sql2.total, 0) as diff
FROM sql1
LEFT JOIN sql2
ON sql1.year = sql2.year
AND sql1.month = sql2.month
3

came here to say this :blush:

to be more specific, this would be

  FROM ( /* your existing query */ ) AS sql1
LEFT OUTER
  JOIN ( /* new similar query for refunds */ ) AS sql2
1 Like
4

I was going to say, I don’t understand the sql1 and sql2 bits but that makes it a little clearer.

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.