I have the following 2 example queries plus their result tables (dummy data) below:

SELECT
        subs.Region
        ,subs.Product
        ,SUM(p.Price) TotalPriceA
    FROM dbo.submission_dtl subs
    JOIN dbo.price_dtl p ON subs.SubmissionNumber = p.SubmissionNumber
    GROUP BY subs.Region, subs.Product

SELECT
            r.Region
            ,r.Product
            ,SUM(rp.Price) TotalPriceB
        FROM dbo.report_dtl r
        JOIN dbo.report_price rp ON r.SubmissionNumber = rp.SubmissionNumber
        GROUP BY r.Region, rp.Product

I want to join them so that the result table resembles this:

But when I used this query, I got a result table that resembled this:

SELECT
            subs.Region
            ,subs.Product
            ,SUM(p.Price) TotalPriceA
            ,rptotal.TotalPriceB
        FROM dbo.submission_dtl subs
        JOIN dbo.price_dtl p ON subs.SubmissionNumber = p.SubmissionNumber
        JOIN 
            (
               SELECT
                r.Product
                ,SUM(rp.Price) TotalPriceB
            FROM dbo.report_dtl r
            JOIN dbo.report_price rp ON r.SubmissionNumber = rp.SubmissionNumber
            GROUP BY rp.Product
            ) rptotal on subs.Product = rptotal.Product
        GROUP BY subs.Region, subs.Product, rptotal.TotalPriceB

When I group the subquery by region as well, I get even worse results...

CodePudding user response：

You can try to use two subquery before join

SELECT t1.Region,
       t1.Product,
       t2.TotalPriceA,
       t1.TotalPriceB
FROM (
    SELECT
        r.Region
        ,r.Product
        ,SUM(rp.Price) TotalPriceB
    FROM dbo.report_dtl r
    JOIN dbo.report_price rp ON r.SubmissionNumber = rp.SubmissionNumber
    GROUP BY r.Region, rp.Product
) t1 INNER JOIN (
    SELECT
        subs.Region
        ,subs.Product
        ,SUM(p.Price) TotalPriceA
    FROM dbo.submission_dtl subs
    JOIN dbo.price_dtl p ON subs.SubmissionNumber = p.SubmissionNumber
    GROUP BY subs.Region, subs.Product
) t2 ON t1.Region = t2.Region AND t1.Product = t2.Product

CodePudding user response：

Perhaps a group by is not what is required here, at least not for the final result. Have you considered using the pivot clause instead? As DRapp stated, you might need a union to combine the two queries. Your group by is only required to sumarise the total values before hand, but the pivot should take care of that.

In this example, I'm using a table variable to consolidate all the information and then the pivot. Take a closer look and you'll realise that one of the columns is having a constant all the time for each query. Also, from experience I know that table variables work better with null columns, regardless of the actual data source.

Declare @myData Table (
  region varchar(max) null,
  product varchar(max) null,
  type varchar(max) null,
  totalPriceA money null
)

--The type is the constant to know whether it's A or B
Insert Into @myData(region, product, type, totalPrice)
Select Region, Product, 'TotalPriceA', Sum(Price)
From <your tables here>
Group By region, product

--Repeat for total B.
Insert Into @myData(region, product, type, totalPrice)
Select Region, Product, 'TotalPriceB', Sum(Price)
From <your tables here>
Group By region, product

--Now myData table has all the information.
--You only need the output format
Select region, product, TotalPriceA, TotalPriceB
From @myData
Pivot (
  Sum(totalPrice)
  For type In ('TotalPriceA', 'TotalPriceB')
) As Result

Hope this helps. As you can see, the constant values in column type become the column titles in the final result. You will get null values if one "cell" in the final table doesn't have a corresponding value for that row/column match.