GROUP BY id is working only in my MySQL Query

Question

I am working on one of my old php projects where I used GROUP BY head_id that was working fine, but now it's showing nothing but blank page while executing. Showing results while I am trying GROUP BY id instead of head_id or anything else.

if(isset($_POST['submit'])) {

  $start = mysqli_real_escape_string($conn, $_POST['start']);
  $end = mysqli_real_escape_string($conn, $_POST['end']);

  $sql = "SELECT *
  FROM bill_info
  WHERE date(updated_on) BETWEEN '$start' AND '$end' AND is_paid='1'
  GROUP BY head_id";
  $res = mysqli_query($conn, $sql);

}


while ($data = mysqli_fetch_array($res)) {
  echo $data['head_id']." - ".$data['amount'];
}

Only if I am trying to GROUP BY id then it's working fine but showing nothing while trying to GROUP BY head_id. There are lots of rows for same head_id, that's why I need to group them, otherwise report is becoming too long.

|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||----------------------------------------------------------- Updated !!! --------------------------------------------------------- |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

The query given below is working fine for me.

$sql = "SELECT head_id, sum(amount), sum(student_no) FROM bill_info WHERE date(updated_on) BETWEEN '$start' AND '$end' AND is_paid='1' GROUP BY head_id";

I have tried to select other columns except head_id or aggregate function, but it doesn't work!

Does this answer your question? [PHP SUM function](https://stackoverflow.com/questions/12271598/php-sum-function) — Aksen P, Dec 24 '19 at 07:05
whats your output? `while ($data = mysqli_fetch_array($res)) { echo $data['head_id']." - ".$data[sum(amount)]; }` — VIKAS KATARIYA, Dec 24 '19 at 07:05
Your query is invalid because it aggregates by `head_id` but then also selects other non aggregate columns. Also, you should look into using [prepared statements in PHP](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php). — Tim Biegeleisen, Dec 24 '19 at 07:06
@VikasKatariya Showing nothing but blank page only, no errors also!!! — Uzzwal Dhali, Dec 24 '19 at 07:09
You need to check the errors being returned from your MySQLi calls. You will almost certainly find that @TimBiegeleisen explanation is the correct one once you look at the actual mysqli error message. — Nick, Dec 24 '19 at 07:27

VIKAS KATARIYA · Answer 1 · 2019-12-24T07:18:03.827

-1

Try This

if(isset($_POST['submit'])) {

  $start = mysqli_real_escape_string($conn, $_POST['start']);
  $end = mysqli_real_escape_string($conn, $_POST['end']);

  $sql = "SELECT id, head_id, is_paid, amount, sum(student_no), sum(amount) AS sum
  FROM bill_info
  WHERE updated_on>='$start'
  AND updated_on<='$end'
  GROUP BY id";
  $res = mysqli_query($conn, $sql);

}


while ($data = mysqli_fetch_array($res)) {
  $sum = $row['sum'];
}
 echo $sum."<br>";

edited Dec 24 '19 at 07:18

answered Dec 24 '19 at 07:09

VIKAS KATARIYA

5,867
3
17
34

why this answer is downvote:? – VIKAS KATARIYA Dec 24 '19 at 07:14
1

It's showing results while changing 'GROUP BY head_id' to 'GROUP BY id' – Uzzwal Dhali Dec 24 '19 at 07:17
yes i miss this.. can you get your answeR? (sum) good question select this answe.r – VIKAS KATARIYA Dec 24 '19 at 07:18
if this answer is helpful then accept this answer thank you – VIKAS KATARIYA Dec 24 '19 at 07:22
'sum(amount) AS sum' and sum(amount) is acting same, but I need to GROUP BY head_id, it was working before, why now behaving like this?? – Uzzwal Dhali Dec 24 '19 at 07:23
Low quality question. Low quality answer. `$sql` invalid in the question and in the answer. `ID column` seems to has unique values, which makes `group by` useless and `sum()` value invalid as well. – Aksen P Dec 24 '19 at 07:23
please select this answer thank yop.. – VIKAS KATARIYA Dec 24 '19 at 07:24
hahaha right Dude. – VIKAS KATARIYA Dec 24 '19 at 07:25
Can you please explain why $sql is invalid ? – Uzzwal Dhali Dec 24 '19 at 07:26

GROUP BY id is working only in my MySQL Query

1 Answers1