Member 13224231 Ответов: 2

Как суммировать строки и столбцы таблицы


 id      fes     bus    snacks    other   total
 =============================================
 1      400     200     50        20      670
 =============================================
 2      350     100     20        0       470
 ==============================================
TOTAL = 750     300     70        290     1140
 =============================================


pls how can i create this kind of  table? where id=1 has its total at the end of the row e.g   id=1 has 400 + 200 + 50 + 20 = 670 and also fees has its total at the end of its the column e.g 400 + 350 = 750
and this should apply to all the table records i will appreciate html, php and sql codes,i am a learner thanks
<pre>
<pre>am so new here! pls pardon my english,


Что я уже пробовал:

<table width="100%">
		  <tr>
			<th >S/N</th> 
			<th>fes</th>
			<th>bus</th>
			<th>snacks</th>
			<th>other</th>
                        <th>total</th>
		  </tr>
		  
		  <?php 
		  $conn=mysqli_connect('localhost','root','','practice');
		  $table=mysqli_query($conn,'SELECT * FROM `try`');
		  while($row=mysqli_fetch_array($table))
		  {
			  $id=$row['id'];
			  $date=$row['fes'];
			  $salary=$row['bus'];
			  $recurrent=$row['snacks'];
			  $repairs_maint=$row['other'];
			  $creditors=$row['total'];	 
		  ?>
		  <tr>
			<td class="sno"><?php echo $id ?></td>
			<td class="mark"><?php echo $fes ?></td>
			<td class="mark"><?php echo $bus ?></td>
			<td class="mark"><?php echo $snacks?></td>
			<td class="mark"><?php echo $other?></td>
			<td class="mark"><?php echo $total?></td>
			
		  <?php } ?>
		  
		  <?php 
		  $add=mysqli_query($conn,'SELECT SUM(fes),SUM(bus),SUM(snacks),SUM(other) ,SUM(total) FROM  `try`');
		  while($row1=mysqli_fetch_array($add))
		  {
			$mark=$row1['SUM(fes)'];
			$mark1=$row1['SUM(bus)'];
			$mark3=$row1['SUM(snacks)'];	
			$mark4=$row1['SUM(other)'];
			$mark5=$row1['SUM(total)'];
		 ?>
		 
		  <tr>
		  <tr></tr>
		  <th>Total  =</th>
			<td><?php echo $mark ?></td>
			<td><?php echo $mark1 ?></td>
			<td><?php echo $mark3 ?></td>
			<td><?php echo $mark4 ?></td>
			<td><?php echo $mark5 ?></td>
			
		  <?php } ?>
		  
		</table>


Please i will appreciate your own code in html,php and sql thanks</pre

2 Ответов

Рейтинг:
1

Santosh kumar Pithani

CREATE TABLE #TEMP(id INT,fes INT,bus INT,snacks INT,other INT);

INSERT INTO #TEMP VALUES(1,400,200,50,20),
                        (2,350,100,20,0);
						

SELECT ISNULL(CAST(ID AS VARCHAR(50)),'TOTAL') AS ID,
       SUM(Fes) AS Fes,SUM(Bus) AS Bus,SUM(Snacks) AS Snacks,
       SUM(Other) AS Other,SUM(Fes+Bus+Snacks+Other) AS TOTAL 
   FROM #Temp 
     GROUP BY CUBE(ID);
---------------------------------------------
ID	Fes	Bus	Snacks	Other	TOTAL
------------------------------------------------
1	400	200	50	20	670
2	350	100	20	0	470
TOTAL	750	300	70	20	1140


Member 13224231

Спасибо @santosh Kumar, Pls можете ли вы вставить этот код в мой код,чтобы заставить его работать у меня возникли проблемы здесь код показывает eror, когда я пытаюсь работать с ним, спасибо за ваш ответ 😊

Santosh kumar Pithani

Конечно!:)