Adding up using Sum()

Question:

---

I have a table which contains invoice lines and I am trying to produce a recordset that will produce a list by client with the total owed by that client but I need to take a field with paid yes or no into account to only display open amounts.

Answer:

---

You want to use sum(), let's assume your current query returns a list that looks like this:

Company PaidYN PriceLine

Company1 NO 100.00
Company1 NO 10.00
Company2 YES 100.00
Company1 YES 50.00
Company2 NO 60.00

What we need to do is tell our database to select a single Company name from our invoice table, for that we use DISTINCT. Then the database also needs to calculate the total Priceline based on a value in the PaidYN field, we do that by using select SUM() AS OutputName and we also give it a WHERE paid is not Yes, resulting in the following query:

SELECT DISTINCT Company, SUM(PriceLine) AS Total_Owed
FROM invoice
WHERE (PaidYN NOT LIKE YES)
GROUP BY Company

This query produces the following output:

Company Total_Owed

Company1 110.00
Company2 60.00

And that is exactly what we wanted !

Have fun playing with SQL commands !