mysql - C# SQL Retrive monthly cost from table and display sum by month in chart or datatable

Question

I have created a winform application which also has a form to save project records as

    proj_id | client_id  | proj_name | proj_start |  proj_end  | proj_cost
    -----------------------------------------------------------------------------
        1   |    11      |   x       | 2020-10-10 | 2021-10-23 |   50000
        2   |    12      |   Y       | 2020-11-16 | 2021-08-25 |   10000
        3   |    01      |   Z       | 2021-02-01 | 2022-01-18 |   25000

I am required to get the cost by each month and depict it graphically. I was able to figure a query to get the monthly cost of each project with

SELECT *,proj_cost/PERIOD_DIFF( EXTRACT(YEAR_MONTH FROM proj_end), EXTRACT(YEAR_MONTH FROM proj_start)) AS month_cost FROM project ORDER BY proj_id

the monthly cost of each project are

• project x = 3846 (rounded)
• project y = 1000
• project z = 2273 (rounded)

How can I depict this in a monthly way. for example with the records I have displayed above if user input to see cost from 2020-10 to 2021-02 it would output something like

2020-10 = SUM(x)
2020-11 = SUM(x,y)
2020-12 = SUM(x,y)
2021-01 = SUM(x,y)
2021-02 = SUM(x,y,z)

I would like to say sorry in advance if there is anything wrong as this is my first question and I am not really sure how things work here

question from:https://stackoverflow.com/questions/66051462/c-sharp-sql-retrive-monthly-cost-from-table-and-display-sum-by-month-in-chart-or

Answer 1

Waitting for answers