sqlmysqlgroup-bymariadbbar-chart

How to group by two different rows in to one matching result


I have to write a query to form a Bar chart. In the bar chart x axis should be grouped based on datetime (dateValue) and y axis should have sum of numbers(stringValue). Both these two fields are custom fields in my task_project_field table which has two entries for this values. How to achieve a result where i can group based on datetime and sum the number

Here is my query:

fiddle


Solution

  • Backwards engineering your tables from the SQL you shared, it seems like you have an EAV design. In essence you have multiple key/value stored in your task_project_field table for each task_id. In this case field_id/string/date_value.

    The best way to deal with this is to LEFT OUTER JOIN to your task_project_field table for each field_id you are wanting to grab. Otherwise you end up needing to aggregate and things are going to get ugly.

    Consider:

    SELECT t.id,
      CONCAT(MONTHNAME(tpf349.dateValue), '-', year(tpf349.dateValue)) as displayKey,
      tpf371.stringValue as countsum
    FROM task as t
      inner join task_membership tm on t.id = tm.task_id
        and tm.deleted_at = 0
        and t.deleted_at = 0
      left join task_project_field as tpf371 on t.id = tpf371.task_id
        and tpf371.deleted_at = 0
        and tpf371.field_id = 371
      left join task_project_field as tpf349 on t.id = tpf349.task_id
        and tpf349.deleted_at = 0
        and tpf349.field_id = 349
    

    Here we join twice to tpf; aliasing it according to the field_id we are interested in. We also include a new condition in the join criteria to specify which field_id we want for this particular join.

    +---------+------------+----------+
    |   id    | displayKey | countsum |
    +---------+------------+----------+
    | 2981641 | April-2024 |      900 |
    | 2981642 | June-2024  |   123456 |
    | 2981643 | May-2024   |     8900 |
    | 2981644 | June-2024  |       90 |
    | 2981645 | May-2024   |       16 |
    +---------+------------+----------+
    

    This output is based on the initial screenshot "output" you shared.

    You can see this in action at this dbfiddle.

    I also have a suspicion that your actual data is more complex and has more edge cases than this. It may be that some form of aggregation is necessary, but given the sample data you've shared, it's merely a hunch. For future question, please share sample data from each table and desired results made up from that sample data. The dbfiddle tool I linked to is a nice step up from that as folks that are interested in helping you can immediately go and write sql against your sample data.