Help with two similar querries in Murach’s SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with two similar querries in Murach’s SQL

Author

Topic

II Traveler II
Starting Member

11 Posts

Posted - 2011-10-25 : 12:15:23

So I have a homework assignment that I'm running into a little trouble with. It's very simple code that I can't seem to wrap my head around, mostly because I'm not entirely sure what they're asking for. Here is the table information:

________________
Invoices Table

InvoiceID |
VendorID |
InvoiceNumber |
InvoiceDate |
InvoiceTotal |
PaymentTotal |
CreditTotal |
TermsID |
InvoiceDueDate |
PaymentDate |
________________|

________________
Vendors table

VendorID |
VendorName |
VendorAdress1 |
VendorAdress2 |
VendorCity |
VendorState |
VendorZipCode |
VendorPhone |__
VendorContactLName |
VendorContactFName |
DefaultTermsID |
DefaultAccountNo |
___________________|

And here is the first question:

1. Write a SELECT statement that answers this question: Which invoices have a PaymentTotal that’s greater than the average PaymentTotal for all paid invoices? Return the InvoiceNumber and InvoiceTotal for each invoice.

It's supposed to return 20 rows, but my code returns 22 rows. Thoughts? Here is my code for it:

SELECT InvoiceNumber, InvoiceTotal
FROM Invoices
WHERE PaymentTotal >
(SELECT Avg(PaymentTotal)
FROM Invoices)

Here is the second question:

2. Write a SELECT statement that answers this question: Which invoices have a PaymentTotal that’s greater than the median PaymentTotal for all paid invoices? (The median marks the midpoint in a set of values; an equal number of values lie above and below it.) Return the InvoiceNumber and InvoiceTotal for each invoice.

Hint: Begin with the solution to exercise 2, then use the ALL keyword in the WHERE clause and code “TOP 50 PERCENT PaymentTotal” in the subquery.

It's supposed to return 51 rows, but my code returns 57 rows. Here is my code for that one:

SELECT InvoiceNumber, InvoiceTotal FROM INVOICES
WHERE PaymentTotaL > ALL
(SELECT TOP 50 PERCENT PaymentTotal
FROM INVOICES
ORDER BY PaymentTotal asc)

Thank you so much for taking the time to look at this! I appreciate it.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-25 : 12:17:55

1. You haven't taken into account paid invoices only in the subquery?
2. ditto?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

II Traveler II
Starting Member

11 Posts

Posted - 2011-10-25 : 16:19:03

quote:
Originally posted by nigelrivett

1. You haven't taken into account paid invoices only in the subquery?
2. ditto?

That's the part I'm not sure how to code: filtering out the paid invoices. I've tried "WHERE InvoiceTotal = PaymentTotal" in the subquerry, but that changes nothing. I'm just at a logic block, more or less.

II Traveler II
Starting Member

11 Posts

Posted - 2011-10-25 : 17:07:46

I figured it out now. Thanks anyway nigelrivett!

Superfinboy
Starting Member

2 Posts

Posted - 2012-07-02 : 15:09:55

I'm actually stuck in the same way this guy was anyone have any ideas?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources