'HAVING' Clause. How do I get rid of the number? AdventureWorks2017/2019-CodePudding

SELECT SUM(SubTotal)/17 AS AverageSales
FROM Sales.SalesOrderHeader 
WHERE SalesPersonID IS NOT NULL

-- 4734570.8343

That query gave me the result for the average amount of sales for each salesperson.

 SELECT SalesPersonID
 , SUM(SubTotal) AS TotalSalesBySalesPerson
 , FirstName   ' '   LastName AS [Sales Person]
 FROM Sales.SalesOrderHeader
 JOIN Person.Person
 ON SalesOrderHeader.SalesPersonID = Person.BusinessEntityID
 WHERE SalesPersonID IS NOT NULL
 GROUP BY SalesPersonID, FirstName, LastName
 HAVING SUM(SubTotal) < 4734570
 ORDER BY TotalSalesBySalesPerson DESC

This query gives me the exact results I'm looking for but I want to replace the '4734570' value with an actual statement. My guess was 'HAVING SUM(SubTotal) < SUM(SubTotal)/17' but that doesn't seem to work.

Can anyone help with this?

SELECT SalesPersonID
 , SUM(SubTotal) AS TotalSalesBySalesPerson
 , FirstName   ' '   LastName AS [Sales Person]
FROM Sales.SalesOrderHeader
JOIN Person.Person
ON SalesOrderHeader.SalesPersonID = Person.BusinessEntityID
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID, FirstName, LastName
HAVING SUM(SubTotal) < SUM(SubTotal)/17
ORDER BY TotalSalesBySalesPerson DESC

This is what I tried. It returned an empty table.

CodePudding user response：

If you queries are correct, you can just replace the value with a subquery to get the result:

SELECT salespersonid,
       Sum(subtotal)              AS TotalSalesBySalesPerson,
       firstname   ' '   lastname AS [Sales Person]
FROM   sales.salesorderheader
       JOIN person.person
         ON salesorderheader.salespersonid = person.businessentityid
WHERE  salespersonid IS NOT NULL
GROUP  BY salespersonid,
          firstname,
          lastname
HAVING Sum(subtotal) < (SELECT Sum(subtotal) / 17 AS AverageSales
                        FROM   sales.salesorderheader
                        WHERE  salespersonid IS NOT NULL)
ORDER  BY totalsalesbysalesperson DESC

Also, you mentioned you were calculating an average... there is an AVG() function you may want to use instead of SUM()/17, but since I don't know your business rules I will leave it to you to explore.

CodePudding user response：

I think a cte (common table expression) would be your friend here.

Please notice the semicolon at the beginning of this untested statement:

; WITH summarizedData /*a temporary name for our summarized data*/ AS
(
   SELECT
      SUM(SubTotal)/17 AS AverageSales
   FROM Sales.SalesOrderHeader 
   WHERE
      SalesPersonID IS NOT NULL
)
SELECT
   SalesPersonID
   , SUM(SubTotal) AS TotalSalesBySalesPerson
   , FirstName   ' '   LastName AS [Sales Person]
FROM Sales.SalesOrderHeader
INNER JOIN Person.Person
   ON SalesOrderHeader.SalesPersonID = Person.BusinessEntityID
, summarizedData -- Include our summarized data here
WHERE SalesPersonID IS NOT NULL
GROUP BY
   SalesPersonID,
   FirstName,
   LastName
HAVING
   SUM(SubTotal) < summarizedData.AverageSales
ORDER BY
   TotalSalesBySalesPerson DESC

Of course, if you really do need the average and not that literal 17, then the query is easier.

SELECT
   SalesPersonID
   , SUM(SubTotal) AS TotalSalesBySalesPerson
   , FirstName   ' '   LastName AS [Sales Person]
FROM Sales.SalesOrderHeader
INNER JOIN Person.Person
   ON SalesOrderHeader.SalesPersonID = Person.BusinessEntityID
WHERE SalesPersonID IS NOT NULL
GROUP BY
   SalesPersonID,
   FirstName,
   LastName
HAVING
   SUM(SubTotal) < AVG(SubTotal)
ORDER BY
   TotalSalesBySalesPerson DESC