title: SQLite - Return 0 if null
body: <p>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.</p>
  
  <p><strong>Details:</strong></p>
  
  <ul>
  <li><em>Using  version 1.4 of the Chinook Database</em>
  (<a href="https://chinookdatabase.codeplex.com/" rel="nofollow">https://chinookdatabase.codeplex.com/</a>).   </li>
  <li><em>SQLite DB Browser</em></li>
  <li><strong><em>Chinook Sqlite AutoIncrementPKs.sqlite</em></strong> file​ in the directory with Chinook files is the database I am working on</li>
  </ul>
  
  <p><strong>Problem Statement:</strong>
  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. </p>
  
  <p>Desired Output: </p>
  
  <p><a href="https://i.stack.imgur.com/rIJW6.png" rel="nofollow"><img src="https://i.stack.imgur.com/rIJW6.png" alt="enter image description here"></a></p>
  
  <p><strong>My Query:</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p>My Output:</p>
  
  <p><a href="https://i.stack.imgur.com/wJUFW.png" rel="nofollow"><img src="https://i.stack.imgur.com/wJUFW.png" alt="enter image description here"></a></p>
  
  <p><strong>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.</strong></p>
  
  <p>How do I print the rows in this condition? 
  I tried with <strong>Coalesce</strong> and <strong>ifnull</strong> functions but I can't get them to work.</p>
  
  <p>I'd really appreciate if someone can modify my query to get matching solutions.
  Thanks!</p>
  
  <p><strong>P.S</strong>: This is the schema of Chinook Database</p>
  
  <p><a href="https://i.stack.imgur.com/iKtya.png" rel="nofollow"><img src="https://i.stack.imgur.com/iKtya.png" alt="enter image description here"></a></p>