group by not working

Questionsgroup by not working
Arvind asked 7 years ago

Hi,

I am getting error when using group by clause in mysql query. Can you help me?

Regards

11 Answers
Abu Ghufran answered 7 years ago

Please share the sql query, and if possible email me sample database dump too, to regenerate case.

Arvind answered 7 years ago

Thanks for the response
SQL query is

SELECT * from (SELECT C.NAME AS NAME, C.DOB,MAX(CE.CHECK_IN) AS CHECK_IN,SUM(CE.BILLING_AMT) AS AMOUNT_SUM , count(CE.ID) AS TOTAL_CHECKIN , C.ADDRESS AS ADDRESS,C.CITY AS CITY,C.STATE AS STATE,C.OUTLET_ID AS OUTLET_ID FROM CUSTOMER C JOIN CUSTOMER_OUTLET_STATUS CO ON CO.CUS_ID=C.ID JOIN CUSTOMER_ENTRY CE ON C.ID=CE.CUS_ID JOIN OUTLET O ON C.OUTLET_ID=O.ID group by C.MOBILE) as o where o.OUTLET_ID=11

Abu Ghufran answered 7 years ago

Is this sql working in phpmyadmin alone ?
Also, what actual error are you getting?

Please share screenshot of error. Email link on this thread or send me email at [email protected]

Arvind answered 7 years ago

Yes it is working oh PHPMYADMIN

DATA is correctly showing in table but there are two errors:

1. Filter stop working
2. Total rows (bottom right ) disply wrong

When i remove group by clause everthing works fine.

Abu Ghufran answered 7 years ago

Hello,

Please send me code + db dump for testing.
I'll be replying after reviewing.

You can email at [email protected]

Navaneethan answered 4 years ago

Hi Abu,
For me Dynamic Group By: option not working for me is used same as per grouping.php as below

code:

$g->select_command = $sql;

$g->set_columns($cols);
$e["on_data_display"] = array("filter_display", null, true);
$g->set_events($e);

function filter_display($data)
{
// grand sum total and show in footer user data
$total = 0;
foreach($data["params"] as $d)
{
$total += $d["store_id"];
}

$data["params"]["userdata"] = array("first_name"=>"Grand Summary","last_name"=>"Total: $".$total);
}
$grid_id = "list";
$out = $g->render("grid_id");

Dynamic Group By:
<select class="chngroup">
<?php foreach($cols as $c) { ?>
<option value="<?php echo $c["name"] ?>"><?php echo $c["title"] ?></option>
<?php } ?>
<option value="clear">Clear</option>
</select>
<script>
$(document).ready(function() {

// show dropdown in toolbar
//jQuery('#list1_pager_left').append('<div style="padding-left: 5px; padding-top:2px; float:left"><select class="chngroup"><option value="clear" >–Select Group By–</option><?php foreach($cols as $c) { if($c["title"] !='Action'){?><option value="<?php echo $c["name"] ?>"><?php echo $c["title"] ?></option><?php }} ?><option value="clear" onChange="frz()">Clear</option> </select></div>');

jQuery(".chngroup").live("change",function()
{alert("hi");
var vl = jQuery(this).val();
if(vl)
{
if(vl == "clear")
jQuery("#<?php echo $grid_id; ?>").jqGrid('groupingRemove',true);
else
jQuery("#<?php echo $grid_id; ?>").jqGrid('groupingGroupBy',vl);
}
});
});
</script>
<br>
<br>

<?php echo $out; ?>

Abu Ghufran answered 4 years ago

$grid_id = "list";
$out = $g->render("grid_id");

should be:

$grid_id = "list";
$out = $g->render($grid_id);

Navaneethan answered 4 years ago

Thank u so much and more thing Is any chance
if display be in grouping
(ex)
red – 5 Item(s)
blue – 10 Item(s)
– 5 Item(s)

i need "Blank- 5 Item(s)" instead of empty space how can i do it

Abu Ghufran answered 4 years ago

You can put an IF condition in select_command sql, so that it replaces "" with term blank.
e.g.
select …. IF(colorField is null, 'blank', colorField) … from table.

Navaneethan answered 4 years ago

Thanks a lot and one more thing during grouping search option is showing error for some fields and some fields working for first grouping only how can i solve this pls help

Abu Ghufran answered 4 years ago

Please share screenshot / error detail and code for review.
You can also email at [email protected]

Your Answer

4 + 20 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?