CODEWITHSUNDEEP
phpmysqlisumsql-order-bybetween
Maevari
Maevari

Reputation: 43

simple echo SUM and Group by from mysqli

I have something that may seem very simple for some people but for the life of me I cannot figure it out (and I've been trying all day long). I have simple DB from which I am trying to get SUM by the client by date. All works fine to the last moment where I add SUM(item) to SELECT - from this point I cannot echo anything. Can anyone help me what is wrong with my code below:

<?php
$q_billing = intval($_GET['q_billing']);

$dblink = mysqli_connect("localhost", "User", "Pass", "Exam_2018");

/* If connection fails throw an error */
if (mysqli_connect_errno()) {

echo "Could  not connect to database: Error: ".mysqli_connect_error();

exit();
}

$sqlquery = "SELECT id, client_id, SUM(item) AS total_sales, date 
                FROM test 
                    WHERE date BETWEEN '".$q_billing."' AND '2018-12-31' 
                        GROUP BY client_id ";

if ($result = mysqli_query($dblink, $sqlquery)) {
    /* fetch associative array */
    while ($row = mysqli_fetch_assoc($result)) {
        echo $row["client_id"]." ".$row['total_sales']."<br />";
    }
    /* free result set */
    mysqli_free_result($result);
}

/* close connection */
mysqli_close
?>

Just a note that q_billing is DATE, which I tried to replace with constant 2018-01-01.

I tried also adding ORDER BY, but it does not make any difference. I cannot see any error either - just a blank div where the echo should be.

Thank you for any help

Upvotes: 1

Views: 210

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

if you group by client_id then you should not select id and date 

  $sqlquery = "SELECT  client_id, SUM(item) AS total_sales
            FROM test 
                WHERE date BETWEEN '".$q_billing."' AND '2018-12-31' 
                    GROUP BY client_id ";

Upvotes: 1

Related Questions