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.
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
WHERE (PaidYN NOT LIKE YES)
GROUP BY Company
This query produces the following output:
And that is exactly what we wanted !
Have fun playing with SQL commands !