vendredi 22 mai 2015

SQLite, Table joins

I am trying to answer this question for an assignment:

"Write a single SQL statement to generate a list of employee names with the number of customers they are managing and the total revenue made from those customers. The query should have 3 columns named as Employee Name, No. of Accounts and Total Revenue. The column Total Revenue should prefixed with a $ sign."

I have this so far:

SELECT e.FirstName || ' ' || e.LastName AS 'Employee Name', COUNT(c.SupportRepId) AS 'No. of Accounts'
FROM Employee e, Customer c
WHERE e.EmployeeId = c.SupportRepId
GROUP BY e.EmployeeId;

Which gives me the correct employee names and no. of accounts, but when i try to add the total revenue:

SELECT e.FirstName || ' ' || e.LastName AS 'Employee Name', COUNT(e.EmployeeId) AS 'No. of Accounts', '$' || ROUND(SUM(i.Total), 2) AS 'Total Revenue' 
FROM Employee e, Customer c, Invoice i
WHERE c.CustomerId = i.CustomerId
AND e.EmployeeId = c.SupportRepId
GROUP BY e.EmployeeId;

It changes my no. of accounts to larger numbers. Any ideas?

Aucun commentaire:

Enregistrer un commentaire