CODEWITHSUNDEEP
sql-servert-sqljoin
RobWantsToLearn
RobWantsToLearn

Reputation: 83

SQL Joins Query One Row with Two Filtered Columns

I am using the WorldWideImporters Microsoft sample database tables. I am trying to return rows that are customers with columns that will show their total sales for 2014, 2015, and the total of the two each in their own columns. I am not allowed to use subqueries of any kind.

For this problem I am trying to solve it like this:

SELECT C.CustomerID
      ,C.CustomerName
      ,ISNULL(SUM(OL.Quantity * OL.UnitPrice), 0.00) AS [2014Sales]
      ,ISNULL(SUM(OLP.Quantity * OLP.UnitPrice), 0.00) AS [2015Sales]
      ,ISNULL(SUM(OL.Quantity * OL.UnitPrice), 0.00)
       + ISNULL(SUM(OLP.Quantity * OLP.UnitPrice), 0.00) AS [TotalSales]
FROM Sales.Customers AS C 
LEFT OUTER JOIN Sales.Orders AS O ON C.CustomerID = O.CustomerID
AND O.OrderDate BETWEEN '2014-01-01' AND '2014-12-31'
LEFT OUTER JOIN Sales.OrderLines AS OL ON O.OrderID = OL.OrderID
LEFT OUTER JOIN Sales.Orders AS OP ON O.CustomerID = C.CustomerID
AND O.OrderDate BETWEEN '2015-01-01' AND '2015-12-31'
LEFT OUTER JOIN Sales.OrderLines AS OLP ON OL.OrderID = O.OrderID
GROUP BY C.CustomerID
        ,C.CustomerName
ORDER BY TotalSales DESC, CustomerID;

I am having a hard time understanding joins. I come from having some object oriented experience and I can't quite wrap my head around relational joins. I can see how I could solve this with subqueries, one for 2015, one for 2014.

As it stands my query is running endlessly, which means that the way I tried to join twice must be trying to combine too many combinations of rows. Any help explaining what is happening here and how to fix my query would be much appreciated.

Upvotes: 0

Views: 128

Answers (1)

CurseStacker
CurseStacker

Reputation: 1079

While editing your query there are some lines that didn't make sense:

You joined to the same tables without indicating in the ON statement their condition.

Notice that you have named Sales.Orders and Sales.OrderLines OP and OLP respectively but still use the criteria O.CustomerID = C.CustomerID and OL.OrderID = O.OrderID.

LEFT OUTER JOIN Sales.OrderLines AS OL ON O.OrderID = OL.OrderID
LEFT OUTER JOIN Sales.Orders AS OP ON O.CustomerID = C.CustomerID
AND O.OrderDate BETWEEN '2015-01-01' AND '2015-12-31'
LEFT OUTER JOIN Sales.OrderLines AS OLP ON OL.OrderID = O.OrderID

See if this works: I just corrected your join criterias.

SELECT C.CustomerID
      ,C.CustomerName
      ,ISNULL(SUM(OL.Quantity * OL.UnitPrice), 0.00) AS [2014Sales]
      ,ISNULL(SUM(OLP.Quantity * OLP.UnitPrice), 0.00) AS [2015Sales]
      ,ISNULL(SUM(OL.Quantity * OL.UnitPrice), 0.00)
       + ISNULL(SUM(OLP.Quantity * OLP.UnitPrice), 0.00) AS [TotalSales]
FROM Sales.Customers AS C 
LEFT OUTER JOIN Sales.Orders AS O ON C.CustomerID = O.CustomerID
                                  AND CONVERT(date, O.OrderDate) BETWEEN '2014-01-01' AND '2014-12-31'
LEFT OUTER JOIN Sales.OrderLines AS OL ON O.OrderID = OL.OrderID
LEFT OUTER JOIN Sales.Orders AS OP ON C.CustomerID = OP.CustomerID
                                   AND CONVERT(date, OP.OrderDate) BETWEEN '2015-01-01' AND '2015-12-31'
LEFT OUTER JOIN Sales.OrderLines AS OLP ON OP.OrderID = OLP.OrderID 
GROUP BY C.CustomerID
        ,C.CustomerName
ORDER BY [TotalSales] DESC, CustomerID;

I also added a CONVERT(date to the date field to ignore the time part.

EDIT:

Without using subquery I think this won't work. So I'll just leave this here in case you can use it already.

SELECT [2014].CustomerID
      ,[2014].CustomerName
      ,[2014Sales]
      ,[2015Sales]
      ,[2014Sales] + [2015Sales] AS [TotalSales]
FROM (
    (SELECT C.CustomerID
           ,C.CustomerName
           ,ISNULL(SUM(OL.Quantity * OL.UnitPrice), 0.00) AS [2014Sales]
     FROM Sales.Customers AS C 
     LEFT OUTER JOIN Sales.Orders AS O ON C.CustomerID = O.CustomerID
     LEFT OUTER JOIN Sales.OrderLines AS OL ON O.OrderID = OL.OrderID
     WHERE O.OrderDate BETWEEN '2014-01-01' AND '2014-12-31'
     GROUP BY C.CustomerID
             ,C.CustomerName) AS [2014]
LEFT OUTER JOIN
    (SELECT C2.CustomerID
           ,C2.CustomerName
           ,ISNULL(SUM(OLP.Quantity * OLP.UnitPrice), 0.00) AS [2015Sales]
     FROM Sales.Customers AS C2
     LEFT OUTER JOIN Sales.Orders AS OP ON OP.CustomerID = C2.CustomerID
     LEFT OUTER JOIN Sales.OrderLines AS OLP ON OP.OrderID = OLP.OrderID
     WHERE OP.OrderDate BETWEEN '2015-01-01' AND '2015-12-31'
     GROUP BY C2.CustomerID
             ,C2.CustomerName) AS [2015] ON [2014].CustomerID = [2015].CustomerId
ORDER BY [2014Sales] + [2015Sales] DESC, CustomerID;

Upvotes: 1

Related Questions