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 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jul 2006
    Posts
    174
    Thanks
    13
    Thanked 1 Time in 1 Post

    How to echo row using 'WITH ROLLUP'

    Hi,

    How do I address the field created by the 'with rollup' condition?

    Code:
    $sql="SELECT hours,area,volunteer FROM hoursworked WITH ROLLUP";
    $rs=mysql_query($sql);
    while($row=mysql_fetch_array($rs){
    echo '
    	"<p>Number of Hours Worked: " . $row['hours'] . "</p><p>Area: " . $row['area'] . "</p><p>Name of Volunteer: " . $row['volunteer'] . "</p>"';
    }
    Thanks,

    Andy

  • #2
    Gütkodierer
    Join Date
    Apr 2009
    Posts
    2,127
    Thanks
    1
    Thanked 426 Times in 424 Posts
    What exactly are you trying to do?
    As far as I know, "WITH ROLLUP" is a modifier of "GROUP BY", which you do not use.
    Furthermore, it should produce additional rows, not additional fields.

  • #3
    Regular Coder
    Join Date
    Jul 2006
    Posts
    174
    Thanks
    13
    Thanked 1 Time in 1 Post
    Yes, it produces an extra row that has as it's key, the sum of the row it affects, but no value. Just the key. My puzzle is how to I address that new row to make us of the sum it has produced?

  • #4
    Gütkodierer
    Join Date
    Apr 2009
    Posts
    2,127
    Thanks
    1
    Thanked 426 Times in 424 Posts
    Well, what more is there to say that I didn't already tell you? "WITH ROLLUP" is a modifier of "GROUP BY", using the former without the latter should produce an error.

    And if you want a sum, you first have to get a sum.

    For example,
    PHP Code:
    $sql="SELECT volunteer, SUM(hours) FROM hoursworked GROUP BY volunteer WITH ROLLUP"
    will give you the sum of all worked hours for each volunteer, and additionally, as the last line of the result set, the sum of all worked hours (by all volunteers).

  • #5
    Regular Coder
    Join Date
    Jul 2006
    Posts
    174
    Thanks
    13
    Thanked 1 Time in 1 Post
    Yes, I understand what you have said. I had thought that using the built in ability to obtain a column sum would save the the step of running a second query. The first to iterate each persons record, the second to obtain the sum.

    I almost had a shortcut.

    However, I will continue to use the two query method.

    Thanks for your help.

    Andy


  •  

    Posting Permissions

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