CODEWITHSUNDEEP
sqladventureworks
notaprogrammer
notaprogrammer

Reputation: 1

SQL Server : AdventureWorksLT 2012

I want to calculate the total order amount per customer (first name, last name company name) and have tried this but it does not seem to work.

Is it a syntax error or do I need to try a different approach?

SELECT 
    FirstName + ' ' + LastName ' Clients Name',
    CompanyName 'Company Name'
FROM
    (SELECT sc.CustomerID
     FROM SalesLT.Customer AS sc
     INNER JOIN SalesLT.SalesOrderHeader oh ON sc.CustomerID = oh.CustomerID

     UNION ALL
    
     SELECT sod.SalesOrderID, SUM(sod.OrderQty) AS totalsales
     FROM SalesLT.SalesOrderDetail sod
     INNER JOIN SalesLT.SalesOrderHeader AS soh ON soh.SalesOrderID = sod.SalesOrderID
    ) SalesLT.Customer AS sc
GROUP BY 
    sc.FirstName,
    sc.LastName,
    sod.totalsales

Upvotes: 0

Views: 606

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23797

First, you are starting a select command with "from" which is wrong. A select command is in the form of (simplified):

Select < fieldList > from < sourceName > < other clauses if any >;

It may be preceded with a CTE but the simplified form is as the above.

Second, your use of union is not only wrong but it has no purpose as well.

You might do it like this:

SELECT
     p.Title, p.FirstName, p.MiddleName, p.LastName, tmp.TotalAmount
FROM Sales.Customer           c
     INNER JOIN Person.Person AS p ON p.BusinessEntityID=c.PersonID
     LEFT JOIN
       (
         SELECT
                  soh.CustomerID, SUM(LineTotal) AS TotalAmount
         FROM     Sales.SalesOrderDetail            AS sod
                  INNER JOIN Sales.SalesOrderHeader AS soh ON soh.SalesOrderID=sod.SalesOrderID
         GROUP BY soh.CustomerID
       )                      tmp ON tmp.CustomerID=c.CustomerID;

Schema and table names are a little different if you you are using lite version but the end result is same.

A not on sum() here: Since we know the relations are 1-to-many, you could do a sum like the above after a relation. But be careful with situations that result in a many-to-many relations. In that case you would be summing the same sum result many times, leading to a wrong result. Thus do the sum separately and the join the result. With a 1-to-many relation (as in SAlesOrderHeader (1) - to - (many) SalesOrderDetail, you can do the sum() with the relation used.

PS: We have a LEFT JOIN there, thinking you want all the customers regardless they have bought something or not. If you don't want them, then it would be an INNER JOIN.

Upvotes: 0

Related Questions