Joining and aggregating data from multiple tables

Question

In customer table there is a SupportID which is the WorkerId in the worker Table, each WorkerId shows which will handle that customer.

Working Name | No. of accounts | total revenue 
----------------------------------------------
John McCoy     20                10,000

• Worker table contains - Firstname, Lastname, EmployeeId
• Receipt table contains - receipt Id, CustomerId,
• ReceiptLine Table contains - receiptlineId, receipt Id, Unitprice, quantity

At the moment I have this code / idea

SELECT FirstName, LastName
FROM Employee A, Invoice B
WHERE A.EmployeeId = B.CustomerId

Answer 1

In this question, you have not mention dependency among worker and receipt table. However, let the dependency column is workerId on table receipt. Now try this, hope you will get your desired result.

select a.firstName, sum(count(b.customerId)) as no_accounts, sum(c.unitPrice *c.quantity) as total_revenue 
from (( worker a join receipt b on a.workerId = b.SupportId) 
join receiptLine c on b.receiptId = c.receiptId) group by a.firstName order by a.firstName;

Answer 2

use GROUP BY to resolve your problem