Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    May 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    select from mysql using sum and min

    I have a simple test table in mysql

    table name appointments
    fields
    name, booked,
    3 entries

    nick, 1
    john, 1
    nick, 1


    I want to echo out the name with the least amounts of booked 1's
    so output should be john

    I have no idea how to do it

  • #2
    New Coder
    Join Date
    May 2005
    Location
    New Zealand
    Posts
    76
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Use GROUP BY on the name and also a sort

    Hi,

    This code will do what you need...

    Code:
    SELECT name, SUM(booked)
    FROM appointments
    GROUP BY name
    ORDER BY sum(booked) ASC LIMIT 1
    If you happened to need the person with the most bookings, you can change the ORDER BY to DESC.

    Cheers,
    DouG.

  • #3
    New to the CF scene
    Join Date
    May 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    $result = mysql_query("SELECT name, SUM(booked) FROM appointments GROUP BY name ORDER BY sum(booked) ASC LIMIT 1");


    echo $row['name'];

    -----------------------------------------

    apache error log

    PHP Notice: Undefined variable: row in /home/wildthin/public_html/test.html on line 14

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    You're missing a fetch there.
    PHP Code:
    $row mysql_fetch_assoc($result); 
    On a side note, you should check the link in my sig to determine which mysql library you will change to. Mysql library is deprecated, and may be gone by the 6.0 or parallel 5.x release (although hard to say how long they will keep it).
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •