Write SQL to Join tables from your test database and show Total Sales Amount ( s

Question

Write SQL to Join tables from your test database and show Total Sales Amount ( sum(SalesAmount) ) and TotalCommissionAmount per Agent and Product .

Product Family Key Field Type Key Field Type Key Field Type ManagerName varchar(30) CommisionsPct float ManagerPhone char(12) AgentName varchar(30) AgentPhone char(12) PK AgentID FK ManagerlD int int ProductDescr varchar(100) Unit Price PK ProductID int ManagerAgent FK FK Key ManagerlD int AgentID int DateAssign|date Sales Order OrderDate date Market Total Amt int PK MarketlD Type MarketDeahar50) FK CustID FK ProductID FK AgentlD PK OrderNo FK S date Customer Customer Market int PK Customer Name varchar(50) FK CustID int int FK MarketlD int Customer Phone char(12) achar(20) Customer Zip Customer ID Calendar PK IS date char S_year S qtr

Explanation / Answer

The SQL is as follows:

Select sum ( Total Amt ) AS TotalSalesAmount, sum ( AgentCommission ) AS TotalCommisionsAmount from Sales Order and AgentCommission

Where sales Order.agentID=Agent.AgentID

group by Agent.AgentID, Sales Order.ProductID

Note: there is no such field called AgentCommission in the picture. I have added a field in agent able, or else the question would be incomplete.

I have answered your question. Please do not forget to give a positive feedback to the answer. Thank you.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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