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:
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.