how to insert data from an existing table, to another (empty) existing table on mysql?

[itay av]

i'm using mysql,

what i'm trying to do is:

There are two tables:

Invoices_Table:

you can see that the key of this table is the date field, This field comes in YYYY-DD-MM format, when you can see that for some months, there is More than a single invoice, for example in January there are invoices 1111,1112).

See the INVOICE TABLE screenshot for illustration.

invoce table

AGG_TABLE

This is a table that is currently empty, the key of this table is the date field, which is also in the format YYYY-DD-MM as in the invoice table. There are two additional fields, TOTALB, TOTALI, which are in INTEGER format. See the AGG_TABLE screenshot to illustrate

enter image description here

My goal is to load the data from the invoice table into the empty table, So that in the empty table there will be a total of 12 records (one entry for each of the months) So that the TOTALB column shows: the total amount for that month and where INVOICE TYPE=B, In the same way,the TOTALI column shows: the total amount for the same month ,Where the INVOICE TYPE =I.

See screenshot: RESULT, which illustrates the desired result after loading.

enter image description here

Note: If there is no record for a particular month (as shown in the screenshot of the invoice table, there are no records for month 11), the AGG_TABLE table still needs a date entry with a sum of 0 for TOTALB, TOTALI columns. This entry was highlighted in yellow in the RESULT screen

Thank you for your help!