1

I have two tables, e.g. "Locations" and "Connections"

"Locations" has values

Id | Dimension
---------------    
 1 |    4
 2 |    8
 3 |    2

"Connections" maintains attributes

Origin | Destination | Value | Distance_KM
-------------------------------------------
   1   |      2      |  500  |     30
   1   |      3      |  100  |     20
   2   |      1      |  100  |     10
   2   |      3      |  300  |     10
   3   |      1      |  100  |     40

I want to create an output with the following Attribute Table. Where "In" correspond to "Destination" from "Connections" and "Out" to "Origin" accordingly.

Id | Dimension | In_Value | In_Count | In_Dist | Out_Value | Out_Count | Out_Dist
----------------------------------------------------------------------------------  
 1 |     4     |    200   |     2    |    50   |    600    |    2      |     50
 2 |     8     |    500   |     1    |    30   |    400    |    2      |     20 
 3 |     2     |    400   |     2    |    30   |    100    |    1      |     40

I can achieve the result that I strive for separately with two queries.

Query 1

SELECT C.Destination, SUM(C.Value) AS In_Value, COUNT(C.Destination) AS In_Count, SUM(C.Distance_KM) AS In_Dist
FROM Connections AS C
GROUP BY C.Destination

Query 2

SELECT C.Origin, SUM(C.Value) AS Out_Value, COUNT(C.Origin) AS Out_Count, SUM(C.Origine_KM) AS Out_Dist
FROM Connections AS C
GROUP BY C.Origin

Nevertheless, there should be only one query that solves my issue, is not it? I tried this but no success.

SELECT L.Id AS Id, L.Dimension AS Dimension, C.In_Value, C.In_Count, C.In_Dist, C.Out_Value, C.Out_Count, C.Out_Dist
FROM   Locations AS L
LEFT   JOIN (
             SELECT C.Destination,
                    SUM(C.Value) AS In_Value,
                    COUNT(C.Destination) AS In_Count,
                    SUM(C.Distance_KM) AS In_Dist
             FROM Connections AS C
             GROUP BY C.Destination
        ) ON L.Id = C.Destination
LEFT JOIN (
           SELECT C.Origin,
                  SUM(C.Value) AS Out_Value,
                  COUNT(C.Origin) AS Out_Count, 
                  SUM(C.Origine_KM) AS Out_Dist
           FROM Connections AS C
           GROUP BY C.Origin
        ) ON L.Id = C.Origin

Basically, I do not know if I eligible to add a second LEFT JOIN ON to the query with already existing LEFT JOIN ON, am I?


References:

3
  • 1
    Specify your DBMS, including version.
    – Akina
    Mar 6, 2019 at 7:09
  • I tried this but no success. Each subquery must have a separate alias unique within the whole query. ON clause must refer to a field with that alias, not a name iof inner table which is (in general) invisible out of the subquery.
    – Akina
    Mar 6, 2019 at 7:11
  • It is nothing to do with DBMS. I am working in a program that supports SQL.
    – Taras
    Mar 6, 2019 at 7:30

2 Answers 2

1
SELECT L.Id AS Id, L.Dimension AS Dimension, 
       sq1.In_Value, sq1.In_Count, sq1.In_Dist, 
       sq2.Out_Value, sq2.Out_Count, sq2.Out_Dist
FROM Locations AS L
LEFT JOIN (
           SELECT C.Destination, 
                  SUM(C.Value) AS In_Value,  
                  COUNT(C.Destination) AS In_Count,  
                  SUM(C.Distance_KM) AS In_Dist
           FROM Connections AS C
           GROUP BY C.Destination
         ) AS sq1 ON L.Id = sq1.Destination
LEFT JOIN (
           SELECT C.Origin,  
                  SUM(C.Value) AS Out_Value,  
                  COUNT(C.Origin) AS Out_Count,  
                  SUM(C.Distance_KM) AS Out_Dist
           FROM Connections AS C
           GROUP BY C.Origin
         ) AS sq2 ON L.Id = sq2.Origin

fiddle

1
  • Thanks! I did not know that there is even a Fiddle for SQL.
    – Taras
    Mar 6, 2019 at 7:34
0

Another possibility is to join Connections to Locations on Locations.Id IN (Connections.Origin, Connections.Destination) and perform a series of conditional aggregations depending on which of the two columns matched.

This is what I have in mind:

SELECT
  l.Id
, l.Dimension
, SUM(CASE l.Id WHEN c.Destination THEN c.Value       END) AS In_Value
, SUM(CASE l.Id WHEN c.Destination THEN 1 ELSE 0      END) AS In_Count
, SUM(CASE l.Id WHEN c.Destination THEN c.Distance_KM END) AS In_Dist
, SUM(CASE l.Id WHEN c.Origin      THEN c.Value       END) AS Out_Value
, SUM(CASE l.Id WHEN c.Origin      THEN 1 ELSE 0      END) AS Out_Count
, SUM(CASE l.Id WHEN c.Origin      THEN c.Distance_KM END) AS Out_Dist
FROM
  Locations AS l
  LEFT JOIN Connections AS c ON l.Id IN (c.Origin, c.Destination)
GROUP BY
  l.Id
, l.Dimension
;

Note: the counts (In_Count, Out_Count) have been implemented using the SUM function, which is merely for consistency with the rest of the code. You can use COUNTs instead, which would look almost the same, just without the ELSE 0 bit:

...
, COUNT(CASE l.Id WHEN c.Destination THEN 1 END) AS In_Count
...
, COUNT(CASE l.Id WHEN c.Origin      THEN 1 END) AS Out_Count
...

Despite looking arguably simpler, this approach may not necessarily be faster, especially on larger datasets. However, in case performance is not an issue, this option might look more readable, and therefore more maintainable.

The solution can be tested online with the help of this fiddle (borrows the test setup from Akina's fiddle).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.