l------l------------------------------l-----------------l--------------------l
l ID l CUSTOMER_ID l NAME l LASTNAME l
l------l------------------------------l-----------------l--------------------l
1 0001 A AA
2 0002 B BB
3 0003 C CC
----------------------------------------------------------------------------
order
l------l--------------------------l-------------------l--------------------l
l ID l CUSTOMER_ID l PRODUCT l PRICE l
l------l--------------------------l-------------------l--------------------l
1 0001 para 2000
2 0001 milk 6000
3 0002 para 1000
4 0002 milk 2000
5 0002 cake 3000
6 0003 milk 4000
-------------------------------------------------------------------------
อยากให้แสดงผลดังนี้
CUSTOMER_ID NAME LASTNAME TOTAL
0001 A AA 8000
0002 B BB 6000
0003 C CC 4000
ตอนนี้ใช้ CODE นี้ แต่ระบบแสดงผลแบบนี้ครับ
CUSTOMER_ID NAME LASTNAME TOTAL
0001 A AA 8000
0002 B BB 8000
0003 C CC 8000
เลยจะสอบถามว่าต้องแก้ CODE ยังไงครับ
Code (PHP)
$sql = "SELECT customer, SUM(PRICE) AS sumprice from order WHERE order.CUSTOMER_ID LIKE customer.CUSTOMER_ID ORDER BY sumprice DESC";
$dbquery=mysql_db_query($dbname, $sql) or die ("Error Query [".$sql."]");
$row=mysql_fetch_assoc($result);
$num_rows = mysql_num_rows($dbquery);
if ($num_rows == 0) {
echo "ดึงรายงานไม่ได้";
}
else
{
while ($array = mysql_fetch_array($dbquery)) {
select c.customer_id, c.name, c.lastname, sum(o.price) as total
from customer as c
inner join `order ` as o
on o.customer_id = c.customer_id
group by c.customer_id, c.name, c.lastname
Load balance : Server 01
