CODEWITHSUNDEEP
databasesqlite
Ravi Kiran
Ravi Kiran

Reputation: 585

SQLite - Return 0 if null

I have an assignment in Database Management Systems in which I have to write queries for given problems. I have 4 problems, of which I solved 3 and stuck with the last one.

Details:

Problem Statement: Write a query to generate a ranked list of employees based upon the amount of money brought in via customer invoices for which they were the support representative. The result set (see figure below) should have the following fields (in order) for all employees (even those that did not support any customers): ID (e_id), first name (e_first name), last name (e_last_name), title (e_title), and invoice total (total_invoices). The rows should be sorted by the invoice total (greatest first), then by last name (alphabetically), then first name (alphabetically). The invoice total should be preceded by a dollar sign ($) and have two digits after the decimal point (rounded, as appropriate); in the case of employees without any invoices, you should output a $0.00, not NULL. You may find it useful to look at the IFNULL, ROUND, and PRINTF functions of SQLite.

Desired Output:

enter image description here

My Query:

Select Employee.EmployeeId as e_id, 
            Employee.FirstName as e_first_name, 
            Employee.LastName as e_last_name, 
            Employee.Title as e_title, 
            '$' || printf("%.2f", Sum(Invoice.Total)) as total_invoices
From Invoice Inner Join Customer On Customer.CustomerId = Invoice.CustomerId
                    Inner Join Employee On Employee.EmployeeId = Customer.SupportRepId
Group by Employee.EmployeeId
Having Invoice.CustomerId in 
(Select Customer.CustomerId From Customer 
Where Customer.SupportRepId in 
        (Select Employee.EmployeeId From Employee Inner Join Customer On Employee.EmployeeId = Customer.SupportRepId)
)
order by sum(Invoice.Total) desc

My Output:

enter image description here

As you can see, the first three rows are correct but the later rows are not printed because employees don't have any invoices and hence EmployeeID is null.

How do I print the rows in this condition? I tried with Coalesce and ifnull functions but I can't get them to work.

I'd really appreciate if someone can modify my query to get matching solutions. Thanks!

P.S: This is the schema of Chinook Database

enter image description here

Upvotes: 0

Views: 6498

Answers (1)

CL.
CL.

Reputation: 180060

It often happens that it is simpler to use subqueries:

SELECT EmployeeId,
       FirstMame,
       LastName,
       Title,
       (SELECT printf("...", ifnull(sum(Total), 0))
        FROM Invoice
        JOIN Customer USING (CustomerId)
        WHERE Customer.SupportRepId = Employee.EmployeeId
       ) AS total_invoices
FROM Employee
ORDER BY total_invoices DESC;

(The inner join could be replaced with a subquery, too.)

But it's possible that you are supposed to show that you have learned about outer joins, which generate a fake row containing NULL values if a matching row is not found:

...
FROM Employee
LEFT JOIN Customer ON Employee.EmployeeId = Customer.SupportRepId
LEFT JOIN Invoice USING (CustomerID)
...

And if you want to be a smartass, replace ifnull(sum(...), 0) with total(...).

Upvotes: 2

Related Questions