Conditionals and Aggregates across Multiple Tables

Question

I have table that looks like the following:

`units`
+----+------+-------+---------------+-------+
| id | tech | jobID |     city      | units |
+----+------+-------+---------------+-------+
|  1 | 1234 | 8535  | San Jose      |     3 |
|  2 | 1234 | 8253  | San Francisco |     4 |
|  3 | 1234 | 2457  | San Francisco |     5 |
|  4 | 1234 | 8351  | Mountain View |     8 |
+----+------+-------+---------------+-------+

and a view that uses this data to do some computations:

`total`
+----+--------+------+-------+
| id |  name  | tech | total |
+----+--------+------+-------+
|  1 | Dan    | 1234 |    12 |
|  2 | Dan SF | 1234 |    12 |
+----+--------+------+-------+ ...

My problem is that I am trying to sum up the amount of units Dan completed in San Francisco and the amount of units he did elsewhere (need to specifically track how many units were completed in SF). However, I'm unsure of how to do this within my select query and if you look at my current total table, you'll see that both total values are simply summing all of the units regardless of city.

I want to get the following:

`total`
+----+--------+------+-------+
| id |  name  | tech | total |
+----+--------+------+-------+
|  1 | Dan    | 1234 |    11 |
|  2 | Dan SF | 1234 |     9 |
+----+--------+------+-------+ ...

I need help writing my SELECT because I'm unsure of how to use CASE to get the desired result. I've tried the following:

SELECT otherTable.name AS name, units.tech AS tech,
(CASE WHEN City = 'SAN FRANCISCO' THEN SUM(units)
      ELSE SUM(units)
) AS total
FROM units, otherTable
GROUP BY name

but clearly this won't work since I'm not differentiating between cities in the two aggregates.

Any help is greatly appreciated.

EDIT: The SELECT query for my current view (with join info) is as follows:

SELECT otherTable.name, units.tech, SUM(units.units)
FROM units
LEFT JOIN otherTable ON otherTable.tech = units.tech
GROUP BY name

As for otherTable, it simply associates each tech ID with a name:

`otherTable`
+----+--------+------+-----------+
| id |  name  | tech | otherInfo |
+----+--------+------+-----------+
|  1 | Dan    | 1234 |    ...... |
+----+--------+------+-----------+

In your query you are cross joining the two tables, so every name is combined with every unit. But in your view sample you show user 'Dan SF' for 'San Francisco' and 'Dan' for others. How is that? Please show how the two tables are related. How do you know it's Dan who completed the units? — Thorsten Kettner, May 17 '16 at 05:32
Sorry about that, I've updated my post with the join information. — mathmorales, May 17 '16 at 05:45
Okay, so the name 'Dan SF' is made up. Use pgreen2's `UNION ALL` query then with adjusted joins. — Thorsten Kettner, May 17 '16 at 06:09

score 1 · Accepted Answer · answered May 17 '16 at 05:34

First off, it appears that your base query is wrong. There isn't nothing about the join between units and otherTable, but I don't know enough to put it in.

It seems strange to me that you would want it broken out into rows instead of columns, but you could do the following:

SELECT otherTable.name AS name, units.tech AS tech,
SUM(units) AS total
FROM units, otherTable
-- not sure if this section should exclude 'SAN FRANCISO' or not
GROUP BY name
UNION ALL
SELECT otherTable.name || ' SF' AS name, units.tech AS tech,
SUM(units) AS total
FROM units, otherTable
WHERE City = 'SAN FRANCISCO'
GROUP BY name

This would give you

+--------+------+-------+
|  name  | tech | total |
+--------+------+-------+
| Dan    | 1234 |    11 |
| Dan SF | 1234 |     9 |
+--------+------+-------+

Or if you want separate columns, you could do this

SELECT otherTable.name AS name, units.tech AS tech,
SUM(units) AS total,
SUM(CASE WHEN City = 'SAN FRANCISCO' THEN units
      ELSE 0
) AS sf_total
FROM units, otherTable
GROUP BY name

This would give you

+--------+------+-------+----------+
|  name  | tech | total | sf_total |
+--------+------+-------+----------+
| Dan    | 1234 |    11 |        9 |
+--------+------+-------+----------+

In my haste I forgot to include the join information. However, this is exactly what I needed since I was unsure of how to utilize CASE in this case and this was sufficient. Thank you very much! — mathmorales, May 17 '16 at 06:16

Conditionals and Aggregates across Multiple Tables

1 Answers1