CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
cee4
cee4

Reputation: 17

Writing a query to display data from two different tables

I'm trying to put one Column as "VendorName" from the Vendors table, and one column as InvoiceAverage from the InvoiceTotal column in the Invoices table. I'm trying to average the invoice totals for each vendor in the table 

SELECT InvoiceTotal
FROM Invoices
GROUP BY Invoices.InvoiceTotal
ORDER BY AVG(InvoiceTotal)

Is as far as i've got and it doesn't give me anything that I want

Upvotes: 0

Views: 285

Answers (2)

Brian
Brian

Reputation: 3713

select VendorName, avg(InvoiceTotal) as 'some column name'
from Invoices
group by VendorName
-- order by avg(InvoiceTotal) desc/ asc

You want to group by the value that you're averaging over. That's how it works.

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147244

Assuming you join on a field VendorId:

SELECT v.VendorName, AVG(i.InvoiceTotal) AS InvoiceAverage
FROM Invoices i
    JOIN Vendors v ON i.VendorId = v.VendorId
GROUP BY v.VendorName
ORDER BY AVG(i.InvoiceTotal)

Upvotes: 1

Related Questions