ขออภัยที่ต้องยกมาขอความช่วยเหลืออีกครั้งครับ พยายามแล้วแต่ไม่สำเร็จ group by และ sum จำนวนแยกชนิด
ขออภัยที่ต้องยกมาขอความช่วยเหลืออีกครั้งครับ พยายามแล้วแต่ไม่สำเร็จ group by และ sum จำนวนแยกชนิด ตามรูปครับCode (PHP) <body>
<?php include('config.php');?>
<form action="" method="post" name="adminForm" id="adminForm">
<table width="57%" height="32" border="0" align="left" cellpadding="0" cellspacing="0" id="gridview">
<tr style="font-family:Arial, Helvetica, sans-serif;font-style:normal;font-weight:normal; font-size:13px;color:black">
<td width="62" align="center"> <span class="ff2">Date : </span></td>
<td width="155">
<input type="text" id= "datepicker" name="date_start" value="<?=$_POST['date_start'];?>"
size="10" style="width:150px;" onchange="document.adminForm.submit();" ></td>
<td width="87" align="right" class="ff2"> Until Date : </td>
<td width="460" align="left" class="ff2">
<input type="text" id= "datepicker2" name="date_stop" value="<?=$_POST['date_stop'];?>"
size="10" style="width:150px;" onchange="document.adminForm.submit();" /></td>
</tr> </table>
</form>
<table width="64%" align="left" border="1" cellpadding="0" cellspacing="0">
<thead>
<tr>
<th height="29" colspan="7" align="left" class="bb2"style="font-family:Arial, Helvetica, sans-serif;font-style:normal; font-size:13px;color:blackgray">Monthly Report</th>
</tr>
<tr bgcolor="#C3C3C3"style="font-family:Arial, Helvetica, sans-serif;font-style:normal;font-weight:normal; font-size:13px;color:black">
<th width="9%" > Customer </th>
<th width="7%" height="29" ><span class="adminlist55"><span class="gf222"><span class="ff2"> Credit</span></span></span></th>
<th width="6%" ><span class="adminlist55"><span class="gf222"><span class="ff2"> Room</span></span></span></th>
<th width="6%" ><span class="ff2">Cash </span></th>
<th width="6%" ><span class="ff2">Package</span></th>
<th width="6%" >House used</th>
<th width="6%" >TOTAL</th>
</tr>
</thead>
<?php
$sql_show = "SELECT customer_table.customer,ots_table.*,
IF(chargedby='Credit',amount,'') AS price1,
IF(chargedby='Room',amount,'') AS price2,
IF(chargedby='Cash',amount,'') AS price3,
IF(chargedby='Package',amount,'') AS price4,
IF(chargedby='House used',amount,'') AS price5
FROM customer_table,ots_table INNER JOIN charge_by_table ON ots_table.chargedby =charge_by_table.charge_by ";
if($_POST)
{
$date_start = $_POST['date_start'];
$date_stop = $_POST['date_stop'];
$Credit="Credit";
$Room="Room";
$Cash="Cash";
$Package="Package";
$House="House used";
$sql_show .=" where (pickup_start_date between '".$_POST['date_start']."' and '".$_POST['date_stop']."')
and chargedby in ('".$Room."' ,'".$Credit."','".$Cash."', '".$Package."', '".$House."')
and ots_table.customeragency = customer_table.customer
GROUP BY customer_table.customer,ots_table.amount ASC";
}
$result_show = mysql_query($sql_show) or die(mysql_error());
$RoomSum= 0;
$CreditSum= 0;
$CashSum= 0;
$PackageSum= 0;
while($rs = mysql_fetch_array($result_show))
{
$Credit = $rs["price1"];
$Room = $rs["price2"];
$Cash = $rs["price3"];
$Package = $rs["price4"];
$House = $rs["price5"];
$customer = $rs["customer"];
$Total = $Credit+$Room+$Cash+$Package+$House;
$CreditTotal += $Credit;
$RoomTotal += $Room;
$CashTotal += $Cash;
$PackageTotal += $Package;
$HouseTotal += $House;
$GrandTotal += $Total;
?>
<tr align="center">
<td height="38"><? echo $customer;?></td>
<td height="38"><? echo $Credit;?></td>
<td height="38"><? echo $Room;?></td>
<td height="38"><? echo $Cash;?></td>
<td height="38"><? echo $Package;?></td>
<td height="38"><? echo $House;?></td>
<td><? echo $Total;?></td>
</tr>
<? } ?>
<tr bgcolor="#C3C3C3"style="font-family:Arial, Helvetica, sans-serif;font-style:normal;font-weight:normal; font-size:13px;color:black">
<th width="9%" bgcolor="#999999">Grand Total</th>
<th width="7%" height="29" ><? echo $CreditTotal;?></th>
<th width="6%" ><? echo $RoomTotal;?></th>
<th width="6%" ><? echo $CashTotal;?></th>
<th width="6%" ><? echo $PackageTotal;?></th>
<th width="6%" ><? echo $HouseTotal;?></th>
<th width="6%" ><? echo $GrandTotal;?></th>
</tr>
</table>
<p> </p>
</body>
<script type="text/javascript">
$(function(){
$("#datepicker").datepicker({ dateFormat: "yy-mm-dd" }).val('<?=$_POST['date_start'];?>')
$("#datepicker2").datepicker({ dateFormat: "yy-mm-dd" }).val('<?=$_POST['date_stop'];?>')
});
</script>
Tag PHP
ประวัติการแก้ไข
Date :
2016-08-19 17:33:01By :
panya@otsView :
760Reply :
4
ถ้า Result แบบนี้จะต้อง Group แต่ล่ะ Type ก่อนครับ แล้วค่อยนำมา JOIN กันครับ เช่นCode (SQL)
SELECT a.CustomerID,b.SumCredit ,c.SumRoom FROM (SELECT CustomerID FROM orders GROUP BY CustomerID) a
LEFT JOIN
-- Credit
(SELECT CustomerID , SUM(Credit) AS SumCredit FROM table_name GROUP BY CustomerID) b INNER JOIN a.CustomerID = b.CustomerID
LEFT JOIN
-- Room
(SELECT CustomerID , SUM(Room) AS SumRoom FROM table_name GROUP BY CustomerID) c INNER JOIN a.CustomerID = c.CustomerID
Date :
2016-08-24 14:30:55By :
mr.win
a,b,c คือ alias name
Date :
2016-08-25 08:09:54By :
fossil31
Server 05
Load balance : Server 05
