SQL Union grouped by formatted strings

Question

I'm trying to sum up 2 columns from 2 different tables, grouped and sorted by a formatted string.

The schema gist is this:

Table A:
* Created At
* Amount Charged

Table B:
* Created At
* Cash Amount

Note they share a column name called "created at", but have different column names for what I'm trying to sum up.

I want a result that says for the month of Jan, the sum of Table A Amount Charged + Table B Cash Amount is x, and for Feb, and so on and so forth.

Here's how I'm formatting the date:

str_to_date(concat(date_format(created_at, '%Y-%m'), '-01')

Please include sample data, table definitions and expected output in your question — M Khalid Junaid, Jun 19 '18 at 05:26

score 1 · Accepted Answer · answered Jun 19 '18 at 04:48

You could use UNION ALL:

SELECT str_to_date(concat(date_format(created_at, '%Y-%m'), '-01'), SUM(sub.c)
FROM (SELECT created_at, Amount AS c
      FROM tabA
      UNION ALL 
      SELECT created_at, Cash
      FROM tabB) sub
GROUP BY str_to_date(concat(date_format(created_at, '%Y-%m'), '-01');

I would replace str_to_date(concat(date_format(created_at, '%Y-%m'), '-01') with EXTRACT(YEAR_MONTH FROM created_at)

SQL Union grouped by formatted strings

1 Answers1