Jump to content

Archived

This topic is now archived and is closed to further replies.

sonal4php

MYSQL with rollup functionality

Recommended Posts

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

Share this post


Link to post
Share on other sites
After fiddling around with a [b]basic[/b] 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.

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

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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


Share this post


Link to post
Share on other sites
You can do half & half... get individual SUMs, then "rollup" yourself.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.