Back to Top
The nuts and bolts for
Creative web development

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 !

Dennis van Galen

Dennis van GalenDennis started his career as order picker in warehouses. In the past 10 years he did alot of data-entry work for Government agencies and around the age of 20 he helped clean the KPN Telecom customer databases. At the age of 27 Dennis returned to KPN Telecom where he was a full time webmaster / webdeveloper. In his spare time he used to be a voluntary Manager for DMXzone.com. After leaving KPN in 2012 Dennis worked for Tevreden.nl on webbased customer satisfaction platforms.

In the past 12 years Dennis became experienced with various webtools, web-languages and database systems.

See All Postings From Dennis van Galen >>

Share this Article

Comments

Adding up time using the SUM()

I have a similar problem and cant find an answer to it i need to add up time thats inserted into my mysql database in the form format of 00:00:00 the trouble i am having is that it is adding it uo incorrectly i.e not rounding the seconds into minutes and the mionutes up into hours etc

 

Help Me any ideas would be great