|
In a previous tip, I introduced an example where each user in a system had their own project table:
Table:
Project
(same structure for user1 and user2) |
| Field
# |
Name |
Type |
| 1 |
ProjectID |
int identity, primary key |
| 2 |
Title |
varchar(50) |
This week's example expands on that by adding a Task table for each user:
Table:
Task
(same structure for user1 and user2) |
| Field
# |
Name |
Type |
|
1
|
TaskID |
int identity, primary key |
|
2
|
ProjectID |
int |
|
3 |
TaskName |
varchar(50) |
|
4 |
Duration |
int |
To find out the total duration of all the tasks in all of user1's projects, I can use:
SELECT SUM(Duration)
FROM user1.Task
If I want to break that down by project, I can use a GROUP BY clause:
SELECT Project.ProjectID, Project.Title, SUM(Duration)
FROM user1.Project LEFT JOIN user1.Task
ON Project.ProjectID=Task.ProjectID
GROUP BY Project.ProjectID, Project.Title
But, what if I want to find the total duration of all of the tasks in all of the
projects in BOTH sets of tables?
I can't use
SELECT SUM(Duration)
FROM user1.Task
UNION ALL
SELECT SUM(Duration)
FROM user2.Task
because that gives me a single row for each user, whereas I want the total for both tables to be
returned in the one row.
The answer, (or at least my answer) is to use a sub query:
SELECT SUM(TotalDuration)
FROM
(
SELECT SUM(Duration) AS TotalDuration
FROM user1.Task
UNION ALL
SELECT SUM(Duration) AS TotalDuration
FROM user2.Task
) AS Totals
Note that the use of a table alias is necessary in this syntax, as is the use of field aliases
for the SUM(Duration) expressions.
|