SQL Statement - SQL Server Forums

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

SQL Statement

Author

Topic

rdoyal
Starting Member

15 Posts

Posted - 2010-10-22 : 13:22:28

Write a Select statement that returns a single value that repersents the sum of the largest unpaid invoices submitted by each vendor. Use a derived table that returns MAX (InvoiceTotal) grouped by VendorID, filtering for Invoices with a balanceDue. Useing a Outer query and a Subquery!

This what I have, it returns 1 row, which is right. But the Value is incorrect. the correct value should be 22101.39 with 1 row. My value is 37966.19 with 1 row.

USE AP
SELECT MAX (InvoiceTotal) AS InvoiceMax
FROM Invoices JOIN
(SELECT VendorID,SUM(InvoiceTotal) AS LargestInv
FROM Invoices
WHERE InvoiceTotal > 0
GROUP BY VendorID ) AS Large
ON Invoices.VendorID = Large.VendorID

Sachin.Nand

2937 Posts

Posted - 2010-10-22 : 14:03:26

Why do I get a feeling that it has something to do with this?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151881

PBUH

rdoyal
Starting Member

15 Posts

Posted - 2010-10-22 : 14:11:57

I don't see anything in this URL you sent that applys to the Satement I have

Sachin.Nand

2937 Posts

Posted - 2010-10-22 : 14:22:55

From the URL I posted before..

quote:
Greetings everyone!

there is 4 SQL exercises I have problems with.. I would appreciate alot for some help.

These are the two tables I need to use for this exercises.

________________
Invoices Table

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

Isn't it a coincidence that you have the same table name,same column names & both of them are homework questions....

PBUH

rdoyal
Starting Member

15 Posts

Posted - 2010-10-22 : 14:37:14

I am just trying to get some help with this one, because I'm coming up with a different Value. I have never used this site ever. As far as others I have no clue who uses this site. My brother told me I should try this.

Obsivus
Starting Member

17 Posts

Posted - 2010-10-22 : 14:39:20

This is the right answer:

SELECT SUM(InvoiceMax) AS SumOfMaximums
FROM (SELECT VendorID, MAX(InvoiceTotal) AS InvoiceMax
      FROM Invoices
      WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0
      GROUP BY VendorID) AS MaxInvoice

your:

quote:

SELECT MAX (InvoiceTotal) AS InvoiceMax 
FROM Invoices JOIN
(SELECT VendorID,SUM(InvoiceTotal) AS LargestInv 
FROM Invoices
WHERE InvoiceTotal > 0 // <- here is the problem 
GROUP BY VendorID ) AS Large 
ON Invoices.VendorID = Large.VendorID

I don't see any reason why your WHERE search condition is " invoicetotal > 0" inside the subquery when you are suppose do filtering with a Balancedue, Join is not necessary to since you are only in need of one table.

@ others, and BTW these are exercises from Murachs SQL SERVER 2008 Book, the database aswell. Homework = When a teacher gives you exercises you have to complete and turn in.. It's abit different.

Sachin.Nand

2937 Posts

Posted - 2010-10-22 : 14:43:38

Ok.

You have to show some sample data & the expected o/p.It's difficult to understand what the problem is just by looking at the query.

PBUH

rdoyal
Starting Member

15 Posts

Posted - 2010-10-22 : 14:51:19

I understand. I was looking for help not the answers. I do thank you for your help! At least I'm showing what I have done so far. I have the concept,just need a little push and I thank you for that! It is not like I have not done anything and wanting the answer. I show how far I have got. Thanks again!

Obsivus
Starting Member

17 Posts

Posted - 2010-10-22 : 14:54:30

quote:
Originally posted by rdoyal

I understand. I was looking for help not the answers. I do thank you for your help! At least I'm showing what I have done so far. I have the concept,just need a little push and I thank you for that! It is not like I have not done anything and wanting the answer. I show how far I have got. Thanks again!

Sorry bout that, it was just a tiny miss you did on the question Just remember Balancedue is invoicetotal-Credittotal-Paymenttotal, there will be more exercises with balancedue :)

rdoyal
Starting Member

15 Posts

Posted - 2010-10-22 : 15:05:22

The answer you gave returns 115 rows. I am only supposed to have 1 row with a value of 22101.39. My value is 37966.19 with 1 row. Here is the question again.
Write a Select statement that returns a single value that repersents the sum of the largest unpaid invoices submitted by each vendor. Use a derived table that returns MAX (InvoiceTotal) grouped by VendorID, filtering for Invoices with a balanceDue. Useing a Outer query and a Subquery!

USE AP
SELECT MAX (InvoiceTotal) AS InvoiceMax
FROM Invoices JOIN
(SELECT VendorID,SUM(InvoiceTotal) AS LargestInv
FROM Invoices
WHERE InvoiceTotal > 0
GROUP BY VendorID ) AS Large
ON Invoices.VendorID = Large.VendorID

(What you gave) 115 rows
SELECT SUM(InvoiceMax) AS SumOfMaximums
FROM (SELECT VendorID, MAX(InvoiceTotal) AS InvoiceMax
FROM Invoices
WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0
GROUP BY VendorID) AS MaxInvoice

Obsivus
Starting Member

17 Posts

Posted - 2010-10-22 : 15:13:36

quote:
Originally posted by rdoyal

The answer you gave returns 115 rows. I am only supposed to have 1 row with a value of 22101.39. My value is 37966.19 with 1 row. Here is the question again.
Write a Select statement that returns a single value that repersents the sum of the largest unpaid invoices submitted by each vendor. Use a derived table that returns MAX (InvoiceTotal) grouped by VendorID, filtering for Invoices with a balanceDue. Useing a Outer query and a Subquery!

USE AP
SELECT MAX (InvoiceTotal) AS InvoiceMax
FROM Invoices JOIN
(SELECT VendorID,SUM(InvoiceTotal) AS LargestInv
FROM Invoices
WHERE InvoiceTotal > 0
GROUP BY VendorID ) AS Large
ON Invoices.VendorID = Large.VendorID

(What you gave) 115 rows
SELECT SUM(InvoiceMax) AS SumOfMaximums
FROM (SELECT VendorID, MAX(InvoiceTotal) AS InvoiceMax
FROM Invoices
WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0
GROUP BY VendorID) AS MaxInvoice

Are you sure? Beacuse I get 1 row with 22101.39 with my answer, weird... Are you using Microsoft SQL Server Management Studio? Which AP database 2005 or 2008?

rdoyal
Starting Member

15 Posts

Posted - 2010-10-22 : 15:16:52

Yes I am correct it is 116 rows. Could you repost? Thanks!

rdoyal
Starting Member

15 Posts

Posted - 2010-10-22 : 15:25:54

Yes Microsoft SQL Server Management Studio, AP 2008.

rdoyal
Starting Member

15 Posts

Posted - 2010-10-22 : 15:30:40

MY bad! I have another query on board also! I'm Sorry!

Obsivus
Starting Member

17 Posts

Posted - 2010-10-22 : 15:49:25

so it worked for you?

rdoyal
Starting Member

15 Posts

Posted - 2010-10-22 : 15:52:03

Yes it did and Thank you very much!!!!

rdoyal
Starting Member

15 Posts

Posted - 2010-10-22 : 15:57:35

SELECT VendorName, MAX(InvoiceNumber) AS InvoiceNumber, MAX(InvoiceTotal) AS Invoicetotal, MIN(InvoiceDate) AS EarliestDate
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
GROUP BY VendorName
ORDER BY EarliestDate DESC

Returns 34 rows, which is right! But I'm not understanding how to do a subquery in the Statement?

Obsivus
Starting Member

17 Posts

Posted - 2010-10-22 : 17:51:42

quote:
Originally posted by rdoyal

SELECT VendorName, MAX(InvoiceNumber) AS InvoiceNumber, MAX(InvoiceTotal) AS Invoicetotal, MIN(InvoiceDate) AS EarliestDate
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
GROUP BY VendorName
ORDER BY EarliestDate DESC

Returns 34 rows, which is right! But I'm not understanding how to do a subquery in the Statement?

Hint: remove the aggregate Funkcions on your columns, and use MIN(invoicedate) in a subquery instead.

btw which country are you from?

rdoyal
Starting Member

15 Posts

Posted - 2010-10-22 : 18:16:27

I'm from the U.S. This has the = Invoices.vendorID Underlined in my outer. I think this a Pseudocode query. PG 203 and PG209 for the exercise #8

SELECT VendorName, InvoiceNumber, InvoiceTotal
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID

(SELECT MIN(InvoiceDate) AS EarliestDate
FROM Invoices JOIN Vendors
ON Vendors.VendorID = Invoices.VendorID
GROUP BY VendorName
ORDER BY EarliestDate DESC)

rdoyal
Starting Member

15 Posts

Posted - 2010-10-23 : 00:39:15

I am trying to do a subquery for this statement that will return 34 rows with the earlier invoice date. I am Useing 2008 AP. My Group is underlined

SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal
FROM Vendors JOIN
(SELECT MIN(InvoiceDate) AS EarliestDate
FROM Invoices JOIN
GROUP BY VendorName, InvoiceNumber, EarliestDate , InvoiceTotal
ORDER BY EarliestDate DESC)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources