Subquery for averages?

Question

I am trying to project InvoiceNbr, InvoiceDt and TotalPrice from the INVOICE table where the TotalPrice is greater than the average TotalPrice.

I've been at for a bit and this is what I've got so far.

SELECT InvoiceNbr, InvoiceDt, TotalPrice

      FROM INVOICE

      GROUP BY InvoiceNBr

      HAVING TotalPrice  > AVG(TotalPrice);

Seems Seems like a fairly simple query but I'm getting syntax errors so I don't really know if I'm approaching it correctly at this point. Any help is greatly appreciated! Thanks!

Answer 1

You need to add all columns into GROUP BY, e.g. both InvoiceDt and TotalPrice also.

SELECT InvoiceNbr, InvoiceDt, TotalPrice
      FROM INVOICE
      GROUP BY InvoiceNBr, InvoiceDt, TotalPrice
      HAVING TotalPrice  > AVG(TotalPrice);

Although what average price you're looking for? The average price for all invoices? If so, try

;with cte as (SELECT InvoiceNbr, InvoiceDt, TotalPrice,
AVG(TotalPrice*1.0) OVER () AS AverageTotalPrice-- average price for all invoices
      FROM INVOICE)

select * from cte where TotalPrice > AverageTotalPrice -- big totals invoices only

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Follow the AdventureWorks2012 example:

WITH CTE AS (SELECT AvgTotal=AVG(Total) FROM ( SELECT  Total=SUM(TotalDue)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID)X )
-- SELECT * FROM CTE  -- 6845522.6274
SELECT SalesPersonID, SUM(TotalDue) AS TotSalesPerson 
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
HAVING SUM(TotalDue) > (SELECT AvgTotal FROM CTE)
ORDER BY TotSalesPerson DESC;

/*
SalesPersonID	TotSalesPerson
NULL	32441339.1228
276	11695019.0605
277	11342385.8968
275	10475367.0751
289	9585124.9477
279	8088694.8538
281	7259567.8761
*/

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Windows Azure SQL Database Programming & Design

Answer 3

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

>> am trying to project invoice_nbr, invoice_date and invoice_amt from the Invoices table where the invoice_amt is greater than the average invoice_amt. <<

Guessing at your DDL, this total column is based on the invoice details. This design error is called a non-normal form redundancy. I also took the liberty of guessing that you actually have more than one invoice in spite of what you posted. Did you notice that your query was wrong because of the invoice_date not being the in GROUP BY? 

Put the total in a VIEW; it will be useful later. You could also use a CTE. 

CREATE VIEW Invoice_Totals
AS
(SELECT invoice_nbr, SUM(unit_price * order_qty) AS invoice_amt_tot
   FROM Invoice_Details
  GROUP BY invoice_nbr);

SELECT invoice_nbr
  FROM Invoice_Totals
 WHERE invoice_amt_tot
       > (SELECT AVG (invoice_amt_tot) FROM Invoice_Totals);

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 4

Thanks for the feedback. I will try these. Sorry if I was a bit unclear.

TotalPrice for all invoices where the TotalPrice is greater than the average TotalPrice.

So I need the average invoice TotalPrice and all invoices greater than the average TotalPrice.

Answer 5

Thanks for the feedback. I will try these. Sorry if I was a bit unclear.

TotalPrice for all invoices where the TotalPrice is greater than the average TotalPrice.

So I need the average invoice TotalPrice and all invoices greater than the average TotalPrice.

Try

with cte as
(SELECT InvoiceNbr, InvoiceDt, avg(TotalPrice) avg_price
      FROM INVOICE
      GROUP BY InvoiceNbr, InvoiceDt)
SELECT InvoiceNbr, InvoiceDt, TotalPrice
      FROM INVOICE a,cte b
      where a.InvoiceNbr=b.InvoiceNbr
      and a.InvoiceDt=b.InvoiceDt
      a.TotalPrice>avg_price;

---

Many Thanks & Best Regards, Hua Min