0

I have two tables: A and B.

Table A has the following set-up:

ID date location sales
1 2022-01-01 1 10000
2 2022-01-02 1 10000
3 2022-01-04 1 10000
... .... 2 ....

So there is no data for for the location 1 at the date 2022-01-03.

Table B has the following set-up:

ID date location budget
1 2022-01-01 1 10000
2 2022-01-03 1 10000
3 2022-01-04 1 10000
... .... 2 ....

So there is no record for location 1 for the date 2022-01-02.

I am trying to join the tables together to get the following output

location sales budget
1 30000 30000
2 ... ...

So I can group it on location and get | location ABC | sales 123 | budget 123 |, which is a sum of all the dates grouped, but also joined the 2 tables together on date and location.

The query I currently have now is as follows:

SELECT SUM(A.sales) AS sales, A.restaurant
FROM A
LEFT OUTER JOIN B ON A.date = B.date AND A.location= B.location
WHERE A.date between ? AND ? 
GROUP BY A.location
UNION
SELECT SUM(B.budget) AS budget, B.restaurant
FROM A
RIGHT OUTER JOIN B ON A.date = B.date AND A.restaurant = B.restaurant
WHERE B.date between ? AND ?  
GROUP BY B.restaurant

I've tried different types of joins and unions and ended up with a query as suggested in this Answer to mimic a full outer join. However, with this query I get the following output:

location column
1 30000
2 ...
3 ...
1 30000
2 ...
3 ...

These sums are correct, but are not in 2 separate columns 'sales' and 'budget'.

Is there a way to achieve this?

4
  • "These sums are correct but not in 2 separate columns 'sales' and 'budget'." - What do you mean by this?...are you trying to net the sales against the budget?
    – J.D.
    Feb 14, 2022 at 21:54
  • Yes indeed, but now to are added on new rows but I want them represented in separate columns Feb 14, 2022 at 22:54
  • Eh? " joined the 2 tables together on date and location" -- But your desired output does not involve "date"??
    – Rick James
    Feb 15, 2022 at 22:54
  • Yes, dates to get the sum within a certain timeframe but the sum itself is the desired output Feb 16, 2022 at 9:40

2 Answers 2

2

In a UNION clause the alignment of the columns in each unioned dataset defines the order of those columns in the final result set.

Since you don't want the sales and budget data points to be in the same column, you can just un-align them and add placeholder default values in the other dataset like so:

SELECT 0 AS budget, SUM(A.sales) AS sales, A.restaurant
FROM A
LEFT OUTER JOIN B ON A.date = B.date AND A.location= B.location
WHERE A.date between ? AND ? 
GROUP BY A.location
UNION
SELECT SUM(B.budget) AS budget, 0 AS sales, B.restaurant
FROM A
RIGHT OUTER JOIN B ON A.date = B.date AND A.restaurant = B.restaurant
WHERE B.date between ? AND ?  
GROUP BY B.restaurant

Then to get the final results you want, you'll want to do one more grouping again on location in a subquery or CTE to aggregate the different sides of the UNION into a single row like so:

SELECT location, SUM(sales) AS sales, SUM(budget) AS budget
FROM 
(
    SELECT 0 AS budget, SUM(A.sales) AS sales, A.location
    FROM A
    LEFT OUTER JOIN B ON A.date = B.date AND A.location= B.location
    WHERE A.date between ? AND ? 
    GROUP BY A.location
    UNION
    SELECT SUM(B.budget) AS budget, 0 AS sales, B.restaurant
    FROM A
    RIGHT OUTER JOIN B ON A.date = B.date AND A.restaurant = B.restaurant
    WHERE B.date between ? AND ?  
    GROUP BY B.restaurant
) Results
GROUP BY location

Note I think I fixed a typo in your first dataset of the UNION clause by changing A.restaurant to A.location.

3
  • Sorry I cannot mark as correct because of my reputation but this works. Thanks so much for the explanation! Feb 15, 2022 at 7:59
  • @user3069332 No problem, glad it works! There's no limitation on marking answers as correct (it's just the ✔️ checkmark button), you just probably can't upvote it which is fine. But no worries either way.
    – J.D.
    Feb 15, 2022 at 11:45
  • I’ve clicked to indicate it as correct, thanks again :) Feb 15, 2022 at 18:04
1

J.D. has explained how to arrange the different aggregates in different columns in the end result. Let me address another issue.

It seems to me that for your problem, not only can you do without a full join, emulated or real, you actually do not need any join at all. You want aggregates per location from one table, and aggregates per location from the other table. And you want the aggregates from the different tables to be on the same row if they are for the same location.

So this is how you can go about it.

  1. Get the aggregates from one table:

    SELECT
      location
    , SUM(sales) AS sales
    FROM
      A
    WHERE
      date BETWEEN ? AND ?
    GROUP BY
      location
    

    You should get something like this:

    location sales
    1 30000
    2 ...
  2. Get the aggregates from the other table:

    SELECT
      restaurant
    , SUM(budget) AS budget
    FROM
      B
    WHERE
      date BETWEEN ? AND ?
    GROUP BY
      restaurant
    

    Output:

    restaurant budget
    1 30000
    2 ...
  3. UNION the two results, applying J.D.'s suggestion of using placeholders:

    SELECT
      location
    , SUM(sales) AS sales
    , 0          AS budget
    FROM
      A
    WHERE
      date BETWEEN ? AND ?
    GROUP BY
      location
    
    UNION ALL
    
    SELECT
      restaurant
    , 0
    , SUM(budget)
    FROM
      B
    WHERE
      date BETWEEN ? AND ?
    GROUP BY
      restaurant
    

    The output in this case should look like this:

    location sales budget
    1 30000 0
    2 ... ...
    ... ... ...
    1 0 30000
    2 ... ...
    ... ... ...
  4. Now all you need is to aggregate the data once more over the combined set:

    SELECT
      location
    , SUM(sales ) AS sales
    , SUM(budget) AS budget
    FROM
      (
        SELECT
          location
        , SUM(sales) AS sales
        , 0          AS budget
        FROM
          A
        WHERE
          date BETWEEN ? AND ?
        GROUP BY
          location
    
        UNION ALL
    
        SELECT
          restaurant
        , 0
        , SUM(budget)
        FROM
          B
        WHERE
          date BETWEEN ? AND ?
        GROUP BY
          restaurant
      ) AS derived
    ;
    

    And that should give you the expected output of

    location sales budget
    1 30000 30000
    2 ... ...

Your Answer

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

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