. SQL . SQL Sum

SQL Sum

SQL Sum() Example

Q: If I were to have an Adam Sandler DVD mini-marathon with the films Big Daddy, The Waterboy, and The Wedding Singer how much entertainment will I be getting, chronographically? 
   
 Select Sum(Film_Length)
   From Film_Table
  Where Film_Title in ("Big Daddy", 
                       "The Waterboy", 
                       "The Wedding Singer");

   
 345  (in minutes, as length is just a numeric)
Comments Comments are left by visitors to FluffyCat.com, are not endorsed by FluffyCat.com, and may or may not be accurate.
Comment by Larry Rate this Comment

Working with time and dates can be tricky and is usually database specific or even database version specific. I found this example in the online MySQL documentation. Essentially, the summing is done for the time using base 100 (like most sums) and is then converted back into time using the SEC_TO_TIME function.

"SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( `time` ) ) ) AS total_time FROM time_table" (posted to the MySQL user section on August 16, 2003 by Stoyan Stefanov)

In my example I just show the example in minutes and leave it at that. If you use a function to convert minutes to time then my example would work for time.

Comment by archive Rate this Comment

Hello,

I use your page http://www.fluffycat.com/sql/index.html very much, because we build DB in school. It has been a great help to me.

I have one question, how can I use sum(time) for example if I have 00:35:20 + 00:30:00 so the result should bee 01:05:20 and not 00:65:20 (hh:mm:ss).

I have seen that you have one example where you Select Sum(Film_Length), my question is how did you build Film_Length table? I so that it is Numeric, but how does it work?

Please answer me (My teacher will "kill" me if I do wrong)

Sign In
to add your own comment