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.

Hi and welcome to SO. You have aliases in your query but they are the same name as the table which kind of defeats the point. The bigger issue is you are using ANSI-89 style joins. Time to start using the "modern" join syntax that has been around now for more than 25 years. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins And don't be scared to add some white space in your code for formatting. Others might be able to decipher it. — Sean Lange, Oct 03 '18 at 14:42
Are you sure that query is doing what you want? You have an OR stuck right in the middle of a whole big pile of AND. There are so many extra parenthesis in here it is hard to follow. — Sean Lange, Oct 03 '18 at 14:46
As far as the query itself I am getting the results I want, I just am unable to sum the array grand_total. I should note that I am not really a programmer. I've been stitching this together and learning as I go — Stephen, Oct 03 '18 at 14:53
I don't think you are really getting the results you want because you have an OR stuck in the middle. It may produce the results currently but there is definitely something amiss there. — Sean Lange, Oct 03 '18 at 14:54
If I change (customer.exclude_sa=0) OR (ord_hedr.ord_type='C') to "AND" I get 0 results. — Stephen, Oct 03 '18 at 15:06
Right because a given row can't be both 'O' and 'S'. I think you have two groups you are looking for there but your parenthesis are not the way you want them. As for the php stuff I can't help you, I can't even spell it correctly. — Sean Lange, Oct 03 '18 at 15:08

Sean Lange · Answer 1 · 2018-10-03T16:19:55.890

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), ())

Thanks for that. It doesn't solve my core issue but it does give me a new direction in coding! The formatting is super clean and it works. I'm looking it over and I'm awestruck (compared to my junk code) thanks so much!. — Stephen, Oct 03 '18 at 15:46
Cool glad it helps. Now maybe somebody with php knowledge can help you with the task at hand. Sadly that isn't me. — Sean Lange, Oct 03 '18 at 15:47
Let me ask you this; is there a way to accomplish that in the query itself? You see I have WEEKNUMBER and my ultimate goal was to total the grand total by week in the month. week 1-5 grand totals and this was my work around. — Stephen, Oct 03 '18 at 15:58
Yeah should be able to that using GROUPING SETS. One sec and I will edit my answer. — Sean Lange, Oct 03 '18 at 16:18
I'll have to play around with it, I'm getting an error: [SQL Server]Incorrect syntax near 'SETS'. ) ) — Stephen, Oct 03 '18 at 16:35
What version of sql server are you using? That was introduced in 2008. — Sean Lange, Oct 03 '18 at 16:36
Ouch. Any chance you can upgrade? 2005 has been out of support for several years now...even 2008R2 is no longer under support. — Sean Lange, Oct 03 '18 at 16:43
Then sadly you are kind of stuck until the upgrade happens. You can get a total row in 2005 but it isn't as easy. You have to get creative to make it happen. It requires a second query and starts to get unwieldy pretty quickly. — Sean Lange, Oct 03 '18 at 16:49
Ok, and so far it isn't working with PHP either. Nonetheless, you've provided a ton of useful insight and direction today. THANK YOU soooo much! — Stephen, Oct 03 '18 at 17:30

Zhorov · Accepted Answer · 2018-10-04T06:44:22.357

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>';

...
?>

PHP array_sum not adding sqlsrv query

2 Answers2