Jump to content


Photo

MYSQL with rollup functionality


  • Please log in to reply
4 replies to this topic

#1 sonal4php

sonal4php
  • Members
  • Pip
  • Newbie
  • 7 posts

Posted 19 July 2006 - 08:39 AM

hi guys
i am new to this forum
and just need some help from u guys
i am using "with rollup" in my mysql query
and it's running perfect
what i want more is to display the 'total' fields in BOLD, which we get b'cuz of "with rollup"
i have tried to pick up those particular entries using php language
but no success
i just wanna know, is there any thing, in the qry, which differentiates between normal rows o/p and total rows o/p
as there r NULL entries for which we have applied GROUP BY
i am using that NULL, but what abt the total count i get, i don't have any means to get the total count differently........
plz help
i mean anything which i can use in my php code

thanx in advance
sonal

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 19 July 2006 - 03:47 PM

After fiddling around with a basic ROLLUP query I was able to use MYSQL to differentiate between a grouped column with a NULL value and the rollup column with the NULL value using the following.

SELECT
IF(group_col IS NULL, 'null_vals', group_col) AS group_col, SUM(val)
FROM
table
GROUP BY group_col
WITH ROLLUP;
eg output
group_col    val
a               10
b               15
null_vals     10
NULL          35

Note that the alias is required. Without it the rollup column is given one of the other columns values. I don't know why (The need for the alias etc) and because I don't quite understand the behaviour I can't recommend using this method.

Perhaps it would be better to use PHP to do the ROLLUP. If you provide an example of the script and the query someone may be able to help.

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 July 2006 - 03:52 PM

Yeah, ROLLUP is a little strange... I would recommend coding it in PHP instead, because you can make it much more flexible this way, and you don't have version compatability issues.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 sonal4php

sonal4php
  • Members
  • Pip
  • Newbie
  • 7 posts

Posted 20 July 2006 - 04:07 AM

thanx guys 4 help
even i understand that php is much more flexible to deal with this
but i have a very long list of records, alongwith different kind of listings at a time
i mean it's something like 7 columns and around 1000 rows
and column needs to be summed up, total is displayed at around 15 times
so to associate each column and make special SUM() for all, is just so time consuming to execute
i thought, it would be much more simpler to use WITH ROLLUP
so i turned to this
well, and i am getting good results even
but only prob is that, i want to display the total count fields in bold, so that user can differentiate it easily
but i guess, i shud do something with php itself
thanx once again 4 ur time

gayatri




#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 July 2006 - 04:04 PM

You can do half & half... get individual SUMs, then "rollup" yourself.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users