Create a Total Line for each State.

[kat35601]

In this report I want to create a line break and a Total line for each State.

 

 while ($row = odbc_fetch_array($result))
{
    echo "<tr><td>" .  $row['cmlstate']."</td>";
    echo "<td>" .  $row['cmlpostcode']."</td>";
    echo "<td>" .  $row['cmlcity']."</td>";
    echo "<td>" .  $row['ompSalesOrderID']."</td>";
    echo "<td>" .  $row['cmoName']."</td>";
    echo "<td>" .  number_format($row['UOMPVOLUMETOTAL'],2)."</td>";
    echo "<td>" .  number_format($row['UOMPTOTALBOXCOUNT'],0)."</td>";
    echo "<td>" .  $row['Hold']."</td></tr>"; 
    

I want to sum 'UOMPVOLUMETOTAL' for each state and create a line break between it and the next state.
    
}

 

[gw1500se]

You need to 'ORDER BY' in your query on state. Then keep a running total of 'UOMPVOLUMETOTAL' until the state changes or you reach the end or your data. At that point output your total and line break and reset the total to 0.

[kat35601]

ok so I add a sum(UOMPVOLUMETOTAL) over(partition by cmlstate) as StateTotal in my query I have my total field. What is the proper way to create a line break and echo the total after each state?

 

thanks

[requinix]

A line break is a <br>.

[kat35601]

LOL thanks

[kat35601]

whats the best way to insert the <br> and StateTotal after each state and before the next state starts?

[requinix]

You don't want to put it inside the table cell?

[kat35601]

I want to insert the statetotal after each state instead of a column.

 

 

[requinix]

Repeating your question for the fourth time does not answer mine.

Do you want to put the total inside the table cell?

[kat35601]

I am sorry yes inside

[requinix]

Then put it

echo "<tr><td>" .  $row['cmlstate']."</td>";

inside the table cell. Stick the <br> and $row[whatever] values in there.

[kat35601]

I want it to break like this so somewhere I have to say when it gets to the end of the state  to echo the total and then start the next state.

 

 

[requinix]

That's not very good table design, but whatever.

You're running a second query to get totals, right? Have the query return a count of rows too. (So AL has 10 rows, AR has 5, and so on.)

Before your loop, start a counter at 0. Increment the counter every time you print a row. When that counter reaches the number of rows the state has, print out a separate <tr> row with the information you want. Then reset the counter to 0 for the next state's rows.

[kat35601]

OK Thanks