Try Tuts+ Premium, Get Cash Back!
Quick Tip: Working with MySQL and SUM
videos

Quick Tip: Working with MySQL and SUM

on Aug 31st 2010 with 39 comments
Tutorial Details
  • Subject: MySQL SUM
  • Length: 4 Minute Video
  • Estimated Difficulty: Beginner
Download Source Files

In today’s quick tip screencast, we’re going to learn a MySQL query trick. You might be already familiar with the SUM() and IF() functions. We will be combining them to come up with some useful queries. This can reduce the number of queries you need to run for fetching certain types of summary data, and also provide a performance improvement.

Premium Members: Download this Video ( Must be logged in)

 
Enjoyed this Post?

Subscribe to our RSS Feed, Follow us on Twitter or simply recommend us to friends and colleagues!

By

Rollover to read this author's bio or click through to see a full list of posts by this author.

Burak Guzel is a full time PHP Web Developer living in Arizona, originally from Istanbul, Turkey. He has a bachelors degree in Computer Science and Engineering from The Ohio State University. He has over 8 years of experience with PHP and MySQL. You can read more of his articles on his website at PHPandStuff.com and follow him on Twitter here.  

Plus Premium
 
 
Note: Want to add some source code? Type <pre><code> before it and </code></pre> after it. Find out more
  • http://laroouse.com piyanistill

    good video but for member

  • eric

    Burak you are awesome.

    I have no idea why I haven’t been visiting php and stuff yet but I assure you I will be frequenting it from here on out.

  • http://vladh.net vladh

    Nice tip!

  • http://vincentghyssens.me Vincent Ghyssens

    Thanks for the tip !
    I had no idea such thing was possible, it’s really great !

  • http://www.damnsemicolon.com Skye

    Nice tip. Never used if() before. Going to have to try it out.

    Could you substitute the “1″ in the if statement for a field? ie. a price.

    So you don’t get the number of rows but actually the total sum of a field?

    And because you’re using 1 and 0 for your values, could you just replace SUM with COUNT and get the same results?

    • http://www.phpandstuff.com/ Burak
      Author

      Sure, you can use a field, or any other expression that returns some kind of value.

  • http://www.phpbuddy.eu phpBuddy

    Most tutorials are about PHP and/or AJAX, so it is really nice and refreshing to see that you actually put the focus on MySQL. Good job and keep it up! :)

  • Brad

    Thank you Burak, MYSQL has such a wonderful set of functions waiting to be found

  • http://www.arvag.net GaVrA

    Great tip! Also thanks for mentioning sakila, i always had problems with(not having) test database’s… :)

  • Garrafa

    thanks, all your sql tutorials are awesome.
    Also thanks for mentioning sakila, i always had problems with(not having) tes. [2]

  • http://www.buddyforever.com Khalil

    Hi, Burak this is really awesome tip Thank you.

    We are waiting for Codeigniter series :)

    And one more request please let me know how to use Codeigniter’s _remap function as
    http://www.facebook.com/buddyforever or http://www.myspace.com/zarpio… they open my profile how can I do this with Codeigniter _remap?

  • http://www.myunv.com/ Sunny Singh

    I learned a lot from this, thanks! Also, I like your accent it’s really soothing, random but please do more quick tips on here.

  • http://www.d3.lt Zy

    Thanks for the tip, was very helpful!

  • Seed

    This is great! Thank you.

  • http://www.codeforest.net/ Codeforest

    Burak, you rock. I knew all this before, but have never see the point or need to use it. Before now.

    Thanks

  • Paul du Long

    Great tip!

  • http://www.thecodebakery.com Sam

    Good to check the field type too. I’ve known people to do SUM’s on varchar fields and once you have 1000+ records it starts to slow things down. MySQL has to convert (cast) each field value to an int/decimal.

    Also once you think you’ve hit the nail in the head test do two things..

    * Test with thousands of records, check the speed. If you don’t do this then you’re asking for trouble down the line when the database fills up with live data

    * Run the SQL but prepend EXPLAIN.. e.g. EXPLAIN SELECT * from tbl WHERE 1=1

    Also careful about running PhpMyAdmin and doing these queries on a live server. Apache can crunch. Best to use a real sql client so it doesn’t affect apache/your visitors.

  • http://www.birkof.ro/ birkof

    Great tip, very helpful for beginners!

  • http://www.odiumediae.de Odiumediae

    Very helpful! These combinations really save a lot of time and code, thanks!

  • Thomas

    Thats really nice!
    If you only want the SUM, you dont really need the if – use boolean expressions :)

    SELECT SUM(IF(foo = “bar”, 1, 0)) AS foobar
    is the same as
    SELECT SUM(foo > bar) as foobar
    because the expression (foo > bar) returns true or false which will be converted to 1 or 0 :)

    • Thomas

      Ups, the second Query should be
      SELECT SUM(foo = “bar”) as foobar

  • andrei

    Thanks ! Great tip .

  • Nikush

    Sweet! Nice tip Burak.

  • Mathew

    Great simple video yet very powerful and useful. Will certainly help me in the future. Thanks for this. NetTuts is so great, the best site out there ever!

  • http://envexlabs.com Matt Vickers

    Awesome tip, Thanks!

  • Khalil

    Thankyou

  • nykeri

    wow awesome tip never used sql in this way sweet

  • subrat

    I can not download the video. Anybody there to help??

  • http://newarts.at Drazen Mokic

    Very useful Tip, thank you.

  • http://www.aediscreative.com Christopher

    I had no idea the if() Select even existed… learn something new everyday.

  • http://www.kameralarim.com güvenlik kameraları

    SUM () of them did not know: /,
    I guess I’m old, I guess I forgot

  • sajid ali

    Please provide alternate version, YouTube is blocked where i am working…

  • http://www.silverjewelryworld.com Discount Silver Jewelry

    That’s wonderful, just keep it up.

  • http://www.blog.rahulprasad.com Rahul Prasad

    Awesome man,
    Is there any more video of such kind?

  • http://www.richardkotze.com Richard Kotze

    Nice one – extremely useful and it is pretty simple to remember. Thanks.

  • http://www.carlosja.com CarlosJa

    That’s pretty cool… However I would rather use PHP If Statement if i needed to find or compare something from the database. It’ll give it a bit more flexibility. but could be useful.

  • http://swordsky.com/ sordsky1

    this is a sweet lovely mysql website, plz visit it, swordsky.

  • dfgfg

    tst

  • Nonagon

    Cool! Good to know!