2. Determine the average customer balance by representative. Show the resulting

Question

2. Determine the average customer balance by representative. Show the resulting average along with the representative's full name as "last name, first name" in a single column, order by the representative's last name

RepNum LastName FirstName Street City State PostalCode Commission Rate Item ItemNum Description On Hand Category Storehouse Entity names Customer Customer Num CustomerName Street City State PostalCode Balance CreditLimit RepNuma Orders Order N OrderDate Column names Customer Num appear inside rectangles Orderline Order N Item Num Numordered Quoted Price An entity appears as a rectangle Line indicates a relationship Absence of a dot indicates the "one" part of the relationship Dot indicates the "many" part of the relationship

Explanation / Answer

ANSWER:

Determine the average customer balance by representative.

SELECT Avg(Balance)
FROM Customer
GROUP BY RepNum;

Show the resulting average along with the representative's full name as "last name, first name" in a single column, order by the representative's last name

SELECT CONCAT(R.Lastname,' ', R.FirstName) AS FULLNAME, Avg(C.Balance)
FROM Rep R JOIN Customer C
ON (R.RepNum = C.RepNum)
ORDER BY R.LastName;

Final Query:
Determine the average customer balance by representative.Show the resulting average along with the representative's full name as "last name, first name" in a single column, order by the representative's last name.

SELECT CONCAT(R.Lastname,' ', R.FirstName) AS FULLNAME, Avg(C.Balance)
FROM Rep R JOIN Customer C
ON (R.RepNum = C.RepNum)
GROUP BY C.RepNum
ORDER BY R.LastName;

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

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