asked
Select statement which ansrews this question!!
Write a SELECT statement that answers this question: Which invoices have a PamenTotal that's greater than the average PaymentTotal for all paid Invoices? Return the InvoiceNumber and InvoiceTotal for each invoice.
Is there one row for each invoice, or is it an invoice detail table where you need to sum multiple rows for each invoice?
I do not think it appropriate to hand you the query, but based on what you have so far you might look at: 1. Moving comparisons out of the column list and into a WHERE clause after the FROM clause 2. Getting your Average payment total for the paid invoices from a subquery (depending on the structure of the table you may need a where clause inside that subquery to ensure you get only the paid ones....)
Go to the Microsoft website, search for **SQL Server Books OnLine**, bookmark it, and then look up HAVING.
SELECT invoice_no, PaymentTotal FROM Invoice WHERE PaymentTotal > (SELECT AVG(PaymentTotal) FROM Invoice WHERE paid = 'Y') AND paid = 'Y'
Hi Oscar, it's not the paid='Y' I was alluding to...the query probably will not compile because you cannot have an aggregate in the WHERE clause with the syntax above
you're right,my fault in my test db I have an existing invoice table so I used invoice2..when I copied your query I only renamed the first leading to Msg 147, Level 15, State 1, Line 3 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. I knew aggregates could appear in the where clause but saw the error and thought something might be slightly off with the syntax
;WITH Invoices AS ( SELECT Invoice_no, PaymentTotal, AVG(PaymentTotal) OVER(PARTITION BY (SELECT NULL))[Average] FROM Invoice WHERE Paid = 'Y' ) SELECT Invoice_no, PaymentTotal FROM Invoices WHERE PaymentTotal > Average
ozamora has the better, more efficient answer--this is just an alternative
