How to build such sql query?

Question

MySQL

Hey!
There are 2 tables: one lists the paints (name of the paint, its characteristics), and the second table records how much such and such a paint arrived and how much left. That is, in the second table there can be many entries about the same paint. Both tables are linked by id. Paint id in the first table = paint id in the second. The task is to make a selection in such a way as to take all the colors from the first table, plus the sum of the inks and the sum of the inks from the second table, and put the difference between these incomings (in and out) there. I tried and wrote the following code, but unfortunately, those paints, which are not recorded in the second table, are not selected. Only those paints are displayed that have at least one entry in the second table. Here is the code:

SELECT paints.*,
        SUM(paints_report.income) as income,
        SUM(paints_report.expense) as expense,
        SUM(income - expense) as total
        FROM paints
        LEFT JOIN paints_report
        ON paints.id = paints_report.paint_id
        ORDER BY paints.name ASC

Please help me improve it

Answer 1

Group by by paint code, summation by quantities and no asterisks
To avoid warnings - wrap summable values ​​in isnull

Answer 2

https://academy.vertabelo.com/blog/introduction-us...
I understand you missed the grouping