All geek questions in one place

Combine 2 mysql queries in one table with unique rows

I am trying to execute a query that displays the total number of hours set for each project in the following format:

Project Name | Hours Project A | 2 Project B | 6 Project C | 8

I have two SQL queries that count the number of hours set for each project. This is necessary because each request has a different JOIN:

For example, if time_records.parent_type = 'Task', then Join:

 INNER JOIN time_records ON time_records.parent_id = project_objects.id

If time_records.parent_type = 'Project', then Join:

 INNER JOIN time_records ON projects.id = time_records.parent_id

I am trying to combine these two queries into one table, however, the problem is that the table repeats the projects, and does not combine them into one unique row for each project (similar to how the pivot table will work).

Here is my current request:

 (SELECT projects.name AS expr1 , sum(time_records.value) AS expr2 FROM project_objects INNER JOIN projects ON projects.id = project_objects.project_id INNER JOIN time_records ON time_records.parent_id = project_objects.id WHERE time_records.parent_type = 'Task' GROUP BY projects.name) UNION ALL (SELECT projects.name AS expr1 , sum(time_records.value) as expr2 FROM projects INNER JOIN time_records ON projects.id = time_records.parent_id WHERE time_records.parent_type = 'Project' GROUP BY projects.name)

Here it outputs:

 Project Name | Hours Project A | 2 Project B | 6 Project C | 8 Project A | 4

I need project A, which should be on one line, showing the value of 6 hours instead of two lines. I tried UNION operation, but that does not help.

+4
source share
2 answers

Can you try it and see if it works?

 SELECT X.expr1, SUM(X.expr2) FROM (SELECT projects.name AS expr1 , sum(time_records.value) AS expr2 FROM project_objects INNER JOIN projects ON projects.id = project_objects.project_id INNER JOIN time_records ON time_records.parent_id = project_objects.id WHERE time_records.parent_type = 'Task' GROUP BY projects.name UNION SELECT projects.name AS expr1 , sum(time_records.value) as expr2 FROM projects INNER JOIN time_records ON projects.id = time_records.parent_id WHERE time_records.parent_type = 'Project' GROUP BY projects.name) X GROUP BY X.expr1
+1
source

use the OR condition to connect

EDIT:

Check this out: most likely it will work for u. Combine the results of two select statements

 SELECT projects.name AS nameval , sum(time_records.value) AS total FROM ((SELECT projects.name AS expr1 , sum(time_records.value) AS expr2 FROM project_objects INNER JOIN projects ON projects.id = project_objects.project_id INNER JOIN time_records ON time_records.parent_id = project_objects.id WHERE time_records.parent_type = 'Task' GROUP BY projects.name) UNION ALL (SELECT projects.name AS expr1 , sum(time_records.value) as expr2 FROM projects INNER JOIN time_records ON projects.id = time_records.parent_id WHERE time_records.parent_type = 'Project' GROUP BY projects.name)) t group by expr1
0
source

Source: https://habr.com/ru/post/1437506/

More articles:


All Articles