Cross database joins with multiple where conditions and group by-CodePudding

I'm not very handy with SQL statements, but I think this is doable. Unfortunately my attempts either fail syntactically or just get stuck and don't return information.

I have two tables in separate databases but on the same server.

In table 1, I have an ID, NAME, DATE, QUANTITY, and COST

db1.schema.table1
 ---- -------- ------------ ---------- ------ 
| ID |  NAME  |    DATE    | QUANTITY | COST |
 ---- -------- ------------ ---------- ------ 
|  1 | Thing1 | 2021-06-01 |        3 |   10 |
|  2 | Thing2 | 2021-07-01 |        1 |    3 |
|  1 | Thing1 | 2021-06-01 |        5 |   10 |
|  3 | Thing3 | 2021-06-01 |        5 |    7 |
|  5 | Thing5 | 2021-06-01 |        3 |   10 |
|  3 | Thing3 | 2021-05-01 |        1 |   10 |
|  3 | Thing3 | 2021-08-01 |        4 |   15 |
 ---- -------- ------------ ---------- ------

In table 2, I have an ID and an INDICATOR

db2.schema.table2
 ---- ----------- 
| ID | INDICATOR |
 ---- ----------- 
|  1 |         1 |
|  2 |         0 |
|  3 |         1 |
|  4 |         1 |
|  5 |         0 |
 ---- -----------

I need to create a transaction cost in table 2, and then sum those costs by grouping by ID. But I only want rows with dates newer than a certain cutoff and ID's that have a non-zero indicator in Table 1. And lastly I order them by total cost descending.

Desired Output:

 ---- -------- ------- 
| ID |  NAME  | TOTAL |
 ---- -------- ------- 
|  3 | Thing3 |    95 |
|  1 | Thing1 |    80 |
 ---- -------- -------

My attempt:

Select a.DATE, 
a.NAME, 
a.ID, 
a.QUANTITY, 
a.COST, 
(COST*QUANTITY) as TRANSACTION, 
SUM(TRANSACTION) as TOTAL
FROM db1.schema.Table1 a
JOIN db2.schema.Table2 b on a.ID = b.ID
WHERE a.DATE > '2021-05-15'
AND b.INDICATOR <> 0
GROUP BY a.ID
ORDER BY [TOTAL] DESC

Any suggestions as to what I'm missing or doing wrong?

CodePudding user response：

Select 
a.ID, 
a.Name,
SUM(COST*QUANTITY) as TOTAL
FROM db1.schema.Table1 a
JOIN db2.schema.Table2 b on a.ID = b.ID
WHERE a.DATE > '2021-05-15'
AND b.INDICATOR <> 0
GROUP BY a.ID,a.Name
ORDER BY [TOTAL] DESC