Need help ASAP please! Instructions: Demonstrate mastery of Aggregate functions,

Question

Need help ASAP please!

Instructions:

Demonstrate mastery of Aggregate functions, grouping, and SQL joins.

What is wrong with this:

SELECT vendor_name,
COUNT(DISTINCT li.account_number) AS "Number of Accounts"
FROM vendors v JOIN invoices i
ON v.vendor_id = i.vendor_id
JOIN invoice_line_items li
ON i.invoice_id = li.invoice_id
GROUP BY vendor_name
HAVING COUNT(DISTINCT li.account_number) > 1
ORDER BY vendor_name

select v.vendor_id, v.vendor_name, count(i.invoice_id)
from invoices i join vendors v
on v.vendor_id = i.vendor_id
group by v.vendor_id, v.vendor_name

select invoice_number, (invoice_total - payment_total) as balance from invoices
where (invoice_total - payment_total) > 0 and
invoice_due_date between GETDATE() and DATEADD(DAY, 30, GETDATE())

select li.account_number as "account number", sum(li.line_item_amount) as "line item total"
from invoice_line_items li
group by li.account_number;

Select COUNT(invoice),AVG(invoice_amount) from Accounts_Details
Where State = 'California' Group by vendors

Select Sum(Invoice ) from Accounts_Details Group by vendors

Select avg(Sum(Invoice )) from Accounts_Details

error I get:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'vendor_id'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'vendor_id'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'vendor_id'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'vendor_id'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'vendor_id'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'vendor_name'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'vendor_id'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'vendor_name'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'invoice_id'.
Msg 207, Level 16, State 1, Line 17
Invalid column name 'invoice_total'.
Msg 207, Level 16, State 1, Line 17
Invalid column name 'payment_total'.
Msg 207, Level 16, State 1, Line 18
Invalid column name 'invoice_due_date'.
Msg 207, Level 16, State 1, Line 18
Invalid column name 'invoice_due_date'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'invoice_number'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'invoice_total'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'payment_total'.

NEED ASAP!

Thank you

Explanation / Answer

1.
SELECT vendor_name,
COUNT(DISTINCT li.account_number) AS "Number of Accounts"
FROM vendors v JOIN invoices i
ON v.vendor_id = i.vendor_id
JOIN invoice_line_items li
ON i.invoice_id = li.invoice_id
GROUP BY vendor_name
HAVING COUNT(DISTINCT li.account_number) > 1
ORDER BY vendor_name

2.
select v.vendor_id, v.vendor_name, count(i.invoice_id)
from invoices i join vendors v
   on v.vendor_id = i.vendor_id
group by v.vendor_id, v.vendor_name

3.
select invoice_number, (invoice_total - payment_total) as balance from invoices
where (invoice_total - payment_total) > 0 and
invoice_due_date between GETDATE() and DATEADD(DAY, 30, GETDATE())

4.
select li.account_number as "account number", sum(li.line_item_amount) as "line item total"
from invoice_line_items li
group by li.account_number;

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.