PHP array_sum not adding sqlsrv query

Question

I am performing the following SQLSRV Query using PHP 5.6 & 7.0.29 and when I get the results I can not seem to add (SUM) the total results of "grand_total" using array_sum or even when I try to loop it.

$sql = "SELECT inv_trx.trx_date, datepart(day, datediff(day, 0, inv_trx.trx_date)/7 * 7)/7 + 1 AS WEEKNUMBER ,  DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,'".$datetime."'), 0))+ 1 AS WEEK_OF_MONTH,   inv_trx.trx_qty, inv_trx.unit_price, inv_trx.ord_qty, item.prod_cat, inv_trx.slsman_1, SUM(inv_trx.trx_qty * inv_trx.unit_price) AS grand_total

FROM dbo.customer customer, dbo.inv_trx inv_trx, dbo.item item, dbo.ord_hedr  ord_hedr

WHERE  datepart(day, datediff(day, 0, inv_trx.trx_date)/7 * 7)/7 + 1 = '1' AND customer.cust_no = inv_trx.cust_no AND customer.cust_no = ord_hedr.cust_no AND inv_trx.order_no = ord_hedr.order_no AND inv_trx.item_no = item.item_no AND 
inv_trx.manu_no = item.manu_no AND ((ord_hedr.ord_type='O') AND (inv_trx.trx_type='S') AND (ord_hedr.ord_class<>'M' And ord_hedr.ord_class<>'P') AND (customer.exclude_sa=0) OR (ord_hedr.ord_type='C') AND (inv_trx.trx_type='S') AND (ord_hedr.ord_class<>'M' And ord_hedr.ord_class<>'P') AND (customer.exclude_sa=0)) AND YEAR(inv_trx.trx_date) = '2018' AND MONTH(inv_trx.trx_date) = '7' GROUP BY  inv_trx.trx_date, DATEPART(WEEK,inv_trx.trx_date), inv_trx.trx_qty, inv_trx.unit_price, inv_trx.ord_qty, item.prod_cat, inv_trx.slsman_1  ";

$params = array();
$result = sqlsrv_query( $conn, $sql );
if( $result === false) {
    die( print_r( sqlsrv_errors(), true) ); 
}

while( $row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC) ) {



 ?>  <tr>
                    <td><?php echo $row['trx_date']->format('m/d/Y')?></td>
                    <td><?php echo $row['WEEKNUMBER']?></td>
                    <td><?php echo $row['trx_qty']?></td>
                    <td><?php echo $row['unit_price']?></td>
                    <td><?php echo $row['ord_qty']?></td>
                    <td><?php echo $row['prod_cat']?></td>
                    <td><?php echo $row['slsman_1']?></td>
                    <td><?php echo $row['grand_total']?></td>
                </tr> 
                 <?php

                  $group = array($row['grand_total']);
                   print_r(array_sum($group));
                  //echo $row['grand_total']; //result is the same

The result of Array_sum is the same is merely echoing the grand_total itself.

example: 4.8 6 109.4 1.197 17.8 45.5 89 instead of total = 184.XX

Is this because I am already using SUM to total inv_trx.trx_qty * inv_trx.unit_price to form grand_total?

I suspect it is an issue with my array but I am not sure. Any advise would be greatly appreciated.

The query Produces the following

Trx Date    Week    Trx QTY Price   Quantity    Category    Salesman    Total
09/02/2018  1         1   4.8        80          MAG          Mickey        4.8
09/02/2018  1         3    2         50          BOB          Donald         6
09/02/2018  1         4    27.35      4         POW           Goose       109.4

What I am trying to do is get the sum of total which is grand_total.

Answer 1

If your T-SQL statement returns rows and you want to sum the values from every row in the resultset, you need to define your array before while () {...} and then add each row's value:

<?php
...

# Statement
$sql = ' ... your statement ...';
$params = array();
$result = sqlsrv_query($conn, $sql, $params);
if ($result === false) {
    die( print_r(sqlsrv_errors(), true)); 
}

# Results
$total = array();
while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
?>  
    <tr>
    <td><?php echo $row['trx_date']->format('m/d/Y')?></td>
    <td><?php echo $row['WEEKNUMBER']?></td>
    <td><?php echo $row['trx_qty']?></td>
    <td><?php echo $row['unit_price']?></td>
    <td><?php echo $row['ord_qty']?></td>
    <td><?php echo $row['prod_cat']?></td>
    <td><?php echo $row['slsman_1']?></td>
    <td><?php echo $row['grand_total']?></td>
    </tr>
<?php
    $total[] = $row['grand_total'];
    echo 'Grand total after current row: '.array_sum($total).'<br>';
}   

# Total after all rows
echo 'Grand total after all rows: '.array_sum($total).'<br>';

...
?>

Answer 2

This is too long for a comment and isn't answering the part your struggling with currently but you entire query could be greatly simplified using aliases, proper join syntax and some formatting to something like this.

SELECT it.trx_date
    , datepart(day, datediff(day, 0, it.trx_date) / 7 * 7) / 7 + 1 AS WEEKNUMBER 
    , DATEPART(WEEK, DATEADD(month, DATEDIFF(month, 0, '".$datetime."'), 0)) + 1 AS WEEK_OF_MONTH
    , it.trx_qty
    , it.unit_price
    , it.ord_qty
    , i.prod_cat
    , it.slsman_1
    , SUM(it.trx_qty * it.unit_price) AS grand_total
FROM dbo.customer c
join dbo.inv_trx it on c.cust_no = it.cust_no 
join dbo.item i on it.item_no = i.item_no 
                    AND it.manu_no = i.manu_no 
join dbo.ord_hedr oh on c.cust_no = oh.cust_no
                    AND it.order_no = oh.order_no 
WHERE  datepart(day, datediff(day, 0, it.trx_date) / 7 * 7) / 7 + 1 = 1
    AND oh.ord_type in ('O', 'C')
    AND it.trx_type = 'S'
    AND oh.ord_class not in ('M', 'P')
    AND c.exclude_sa = 0 
    AND YEAR(it.trx_date) = 2018 --date functions return integers not strings
    AND MONTH(it.trx_date) = 7 
GROUP BY it.trx_date
    , DATEPART(WEEK, it.trx_date)
    , it.trx_qty
    , it.unit_price
    , it.ord_qty
    , i.prod_cat
    , it.slsman_1 

--EDIT--

Adding an example of GROUPING SETS.

I would take a look at this article for a great explanation of how this works.

SELECT it.trx_date
    , datepart(day, datediff(day, 0, it.trx_date) / 7 * 7) / 7 + 1 AS WEEKNUMBER 
    , DATEPART(WEEK, DATEADD(month, DATEDIFF(month, 0, '".$datetime."'), 0)) + 1 AS WEEK_OF_MONTH
    , it.trx_qty
    , it.unit_price
    , it.ord_qty
    , i.prod_cat
    , it.slsman_1
    , SUM(it.trx_qty * it.unit_price) AS grand_total
FROM dbo.customer c
join dbo.inv_trx it on c.cust_no = it.cust_no 
join dbo.item i on it.item_no = i.item_no 
                    AND it.manu_no = i.manu_no 
join dbo.ord_hedr oh on c.cust_no = oh.cust_no
                    AND it.order_no = oh.order_no 
WHERE  datepart(day, datediff(day, 0, it.trx_date) / 7 * 7) / 7 + 1 = 1
    AND oh.ord_type in ('O', 'C')
    AND it.trx_type = 'S'
    AND oh.ord_class not in ('M', 'P')
    AND c.exclude_sa = 0 
    AND YEAR(it.trx_date) = 2018 --date functions return integers not strings
    AND MONTH(it.trx_date) = 7 
GROUP BY GROUPING SETS((it.trx_date
    , DATEPART(WEEK, it.trx_date)
    , it.trx_qty
    , it.unit_price
    , it.ord_qty
    , i.prod_cat
    , it.slsman_1), ())