group by not working

Questionsgroup by not working
Arvind asked 6 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 6 years ago

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

Arvind answered 6 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 6 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 6 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 6 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 3 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 3 years ago

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

should be:

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

Navaneethan answered 3 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 3 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 3 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 3 years ago

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

Your Answer

11 + 7 =

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 / 5. Vote count:

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

Let us improve this post!