CODEWITHSUNDEEP
sqlsql-serversum
ntalekt
ntalekt

Reputation: 852

SUM Two Different Columns in two different tables Grouped by Column in third table

I can get it to work in two seperate queries but I can't do both joins to have all three columns. Can someone help me out please? I need the output something like this:

+------------------------------------------------+
| CLUSTER_NAME   | TOTAL_UNITS   |ALLOCATED_UNITS|
+------------------------------------------------+
|    CLUSTER1    | 300           |25             |
+------------------------------------------------+
|    CLUSTER2    | 400           |45             |
+------------------------------------------- ----+

Two seperate queries are working:

Query 1:

SELECT     CLUSTER_INFO.CLUSTER_NAME, sum(HOST_INFO.UNIT_COUNT) as 'TOTAL_UNITS'
FROM         CLUSTER_INFO INNER JOIN 
HOST_INFO ON HOST_INFO.CLUSTER_ID = CLUSTER_INFO.CLUSTER_ID
GROUP BY CLUSTER_INFO.CLUSTER_NAME

Query 2:

SELECT     CLUSTER_INFO.CLUSTER_NAME, SUM(VM_INFO.UNIT_COUNT) as 'ALLOCATED_UNITS'
FROM         CLUSTER_INFO INNER JOIN 
VM_INFO ON CLUSTER_INFO.CLUSTER_ID = VM_INFO.CLUSTER_ID
GROUP BY CLUSTER_INFO.CLUSTER_NAME

Table 1 (CLUSTER_INFO):

+--------------------------------+
| CLUSTER_NAME   | CLUSTER_ID    |
+--------------------------------+
|    CLUSTER1    | 1             |
+--------------------------------+
|    CLUSTER2    | 2             |
+--------------------------------+

Table 2 (HOST_INFO):

+------------------------------------------------+
| HOST_NAME      | CLUSTER_ID    | UNIT_COUNT    |
+------------------------------------------------+
|    HOST1       | 1             | 150           |
+------------------------------------------------+
|    HOST2       | 1             | 150           |
+------------------------------------------------+
|    HOST3       | 2             | 200           |
+------------------------------------------------+
|    HOST4       | 2             | 200           |
+------------------------------------------------+

Table 3 (VM_INFO):

+------------------------------------------------+
| VM_NAME        | CLUSTER_ID    | UNIT_COUNT    |
+------------------------------------------------+
|    VM1         | 1             | 10            |
+------------------------------------------------+
|    VM2         | 1             | 15            |
+------------------------------------------------+
|    VM3         | 2             | 20            |
+------------------------------------------------+
|    VM4         | 2             | 25            |
+------------------------------------------------+

http://www.sqlfiddle.com/#!2/5f9614/5

Upvotes: 1

Views: 420

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

It is safest to do this using two subqueries:

SELECT ci.CLUSTER_NAME, hi.TOTAL_UNITS, vi.ALLOCATED_UNITS
FROM CLUSTER_INFO ci LEFT JOIN 
     (SELECT hi.CLUSTER_ID, SUM(hi.UNIT_COUNT) as TOTAL_UNITS
      FROM HOST_INFO hi
      GROUP BY hi.CLUSTER_ID
     ) hi
     ON ci.CLUSTER_ID = hi.CLUSTER_ID LEFT JOIN
     (SELECT vi.CLUSTER_ID, SUM(vi.UNIT_COUNT) as ALLOCATED_UNITS
      FROM VM_INFO vi
      GROUP BY vi.CLUSTER_ID
     ) vi
     ON ci.CLUSTER_ID = vi.CLUSTER_ID ;

I introduced table aliases to make the query more readable. And, don't put column aliases in single quotes. You don't need to quote these names at all. Only use single quotes for date and string constants.

This SQL Fiddle shows it working.

Upvotes: 1

Related Questions