Combining two select statements into one SQL Server CTE

Question

I am trying to create a CTE (or possible an alternative) where I take two existing select statements and join the to compare. Both of these statements already work as expected and return the desired results, however I would like to compare some of the columns in the results.

Query A:

SELECT
    Region.RegionID,
    Country.CountryName,
    Segment.SegmentName,
    SalesOrder.SalesMonth,
    FORMAT(SUM(SalesOrderLineItem.SalePrice), 'n') AS [SalePrice]
FROM
    Country
    INNER JOIN Region ON Region.CountryID = Country.CountryID
    INNER JOIN Segment ON Segment.SegmentID = Region.SegmentID
    INNER JOIN SalesRegion ON SalesRegion.RegionID = Region.RegionID
    INNER JOIN SalesOrder ON SalesOrder.SalesRegionID = SalesRegion.SalesRegionID
    INNER JOIN SalesOrderLineItem ON SalesOrderLineItem.SalesOrderID = SalesOrder.SalesOrderID
    INNER JOIN Product ON Product.ProductID = SalesOrderLineItem.ProductID
    INNER JOIN ProductCost ON Product.ProductID = ProductCost.ProductID
    AND ProductCost.CountryID = Country.CountryID
GROUP BY
    Region.RegionID,
    Segment.SegmentName,
    Country.CountryName,
    SalesOrder.SalesMonth

Query B:

    SELECT
    Country.CountryName,
    Segment.SegmentName,
    SalesKPI.SalesYear,
    FORMAT(SUM(SalesKPI.KPI), 'n') AS [KPI]
FROM
    SalesKPI
    INNER JOIN SalesPerson ON SalesKPI.SalesPersonID = SalesPerson.SalesPersonID
    INNER JOIN SalesRegion ON SalesRegion.SalesPersonID = SalesPerson.SalesPersonID
    INNER JOIN Region ON Region.RegionID = SalesRegion.RegionID
    INNER JOIN Segment ON Segment.SegmentID = Region.SegmentID
    INNER JOIN Country ON Country.CountryID = Region.CountryID
GROUP BY
    SalesKPI.SalesYear,
    Country.CountryName,
    Segment.SegmentName

Query A:

Query B:

I have tried the following code, however the sales and KPI (estimate) values are far higher than what they are supposed to be:

WITH sales_cte AS (
        SELECT
            Region.RegionID,
            Country.CountryName,
            Segment.SegmentName,
            SalesOrder.SalesMonth,
            SUM(SalesOrderLineItem.SalePrice) AS SalePrice
        FROM
            Country
            INNER JOIN Region ON Region.CountryID = Country.CountryID
            INNER JOIN Segment ON Segment.SegmentID = Region.SegmentID
            INNER JOIN SalesRegion ON SalesRegion.RegionID = Region.RegionID
            INNER JOIN SalesOrder ON SalesOrder.SalesRegionID = SalesRegion.SalesRegionID
            INNER JOIN SalesOrderLineItem ON SalesOrderLineItem.SalesOrderID = SalesOrder.SalesOrderID
            INNER JOIN Product ON Product.ProductID = SalesOrderLineItem.ProductID
            INNER JOIN ProductCost ON Product.ProductID = ProductCost.ProductID
            AND ProductCost.CountryID = Country.CountryID
        GROUP BY
            Region.RegionID,
            Segment.SegmentName,
            Country.CountryName,
            SalesOrder.SalesMonth
    ),
    kpi_cte AS (
        SELECT
            Country.CountryName,
            Segment.SegmentName,
            SalesKPI.SalesYear,
            SUM(SalesKPI.KPI) AS [KPI]
        FROM
            SalesKPI
            INNER JOIN SalesPerson ON SalesKPI.SalesPersonID = SalesPerson.SalesPersonID
            INNER JOIN SalesRegion ON SalesRegion.SalesPersonID = SalesPerson.SalesPersonID
            INNER JOIN Region ON Region.RegionID = SalesRegion.RegionID
            INNER JOIN Segment ON Segment.SegmentID = Region.SegmentID
            INNER JOIN Country ON Country.CountryID = Region.CountryID
        GROUP BY
            SalesKPI.SalesYear,
            Country.CountryName,
            Segment.SegmentName
    )
SELECT
    sales_cte.RegionID,
    sales_cte.CountryName,
    sales_cte.SegmentName,
    YEAR(sales_cte.SalesMonth),
    FORMAT(SUM(SalePrice), 'n') AS [Sales],
    FORMAT(SUM(kpi_cte.KPI), 'n') AS [Estimate]
FROM
    sales_cte
    INNER JOIN kpi_cte ON sales_cte.CountryName = sales_cte.CountryName
    AND sales_cte.SegmentName = kpi_cte.SegmentName
GROUP BY
    RegionID,
    sales_cte.CountryName,
    sales_cte.SegmentName,
    YEAR(SalesMonth)
ORDER BY
    YEAR(sales_cte.SalesMonth),
    sales_cte.CountryName ASC,
    sales_cte.RegionID

Query C:

An example of what it should look like is as follows:

RegionID	CountryName	SegmentName	Date	Sales	Estimate (KPI)
2	Canada	Midmarket	2001	792,579.90	777,009.00

Is there a reason that the sales and estimate (kpi) are returning the wrong values even though its the same initial select statements?

[Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) — philipxy, Aug 22 '22 at 07:58
Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Aug 22 '22 at 07:59
This seems likely to be a common error where people want some joins, each possibly involving a different key, of some subqueries, each possibly involving join and/or aggregation, but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations. Write separate aggregate subqueries over appropriate rows and/or aggregate (maybe distinct) case statements picking rows of (possibly join result) tables; then join the subqueries on keys. [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/q/12464037/3404097) — philipxy, Aug 22 '22 at 08:32

score 1 · Accepted Answer · answered Aug 22 '22 at 07:49

erm, how about just

SELECT
         M.[RegionID],
         M.[CountryName],
         M.[SegmentName],
         M.[SalesMonth],
         M.[SalePrice],
         Y.[SaleYear],
         Y.[KPI]
   FROM
         (
         SELECT
                  Region.RegionID,
                  Country.CountryName,
                  Segment.SegmentName,
                  SalesOrder.SalesMonth,
                  FORMAT(SUM(SalesOrderLineItem.SalePrice), 'n') AS [SalePrice]
            FROM
                  Country
               INNER JOIN
                  Region
                     ON Region.CountryID = Country.CountryID
               INNER JOIN
                  Segment
                     ON Segment.SegmentID = Region.SegmentID
               INNER JOIN
                  SalesRegion
                     ON SalesRegion.RegionID = Region.RegionID
               INNER JOIN
                  SalesOrder
                     ON SalesOrder.SalesRegionID = SalesRegion.SalesRegionID
               INNER JOIN
                  SalesOrderLineItem
                     ON SalesOrderLineItem.SalesOrderID = SalesOrder.SalesOrderID
               INNER JOIN
                  Product
                     ON Product.ProductID = SalesOrderLineItem.ProductID
               INNER JOIN
                  ProductCost
                     ON Product.ProductID = ProductCost.ProductID
                        AND ProductCost.CountryID = Country.CountryID
            GROUP BY
                  Region.RegionID,
                  Segment.SegmentName,
                  Country.CountryName,
                  SalesOrder.SalesMonth
         ) M
      JOIN
         (
         SELECT
                  Country.CountryName,
                  Segment.SegmentName,
                  SalesKPI.SalesYear,
                  FORMAT(SUM(SalesKPI.KPI), 'n') AS [KPI]
            FROM
                  SalesKPI
               INNER JOIN
                  SalesPerson
                     ON SalesKPI.SalesPersonID = SalesPerson.SalesPersonID
               INNER JOIN
                  SalesRegion
                     ON SalesRegion.SalesPersonID = SalesPerson.SalesPersonID
               INNER JOIN
                  Region
                     ON Region.RegionID = SalesRegion.RegionID
               INNER JOIN
                  Segment
                     ON Segment.SegmentID = Region.SegmentID
               INNER JOIN
                  Country
                     ON Country.CountryID = Region.CountryID
            GROUP BY
                  SalesKPI.SalesYear,
                  Country.CountryName,
                  Segment.SegmentName
         ) Y
            ON Y.[CountryName] = M.[CountryName]
               AND Y.[SegmentName] = M.[SegmentName]

Thank you for the response. This required a little bit of modification, but provided me with good guidance on getting the final result — dwewers, Aug 22 '22 at 10:42

Combining two select statements into one SQL Server CTE

1 Answers1