时间戳

首页 > 解决方案 > Mysql加入，计数和求和？

mysql - Mysql加入，计数和求和？

问题描述

活动表是待售商品，交易表上是已售商品。我想计算制作交易表的transaction_id并计算已售商品的总和..但表格不同..

这是我尝试过的：

$uid = $_GET['uid'];

$query = "
  SELECT
    transaction.*, 
    count(*) AS transaction.transaction_id,
    sum(*) AS campaign.payout 
  FROM transaction 
  JOIN transaction.c_id=campaign.c_id 
  GROUP_BY c_id 
  WHERE uid=$uid AND DATE(dateTime)=DATE(NOW())";

$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($result))

{$transaction_id = $row['transaction_id'];
 $payout = $row['payout'];}  
echo "payment: [".$payout."] transaction: [".$transaction_id."]";

竞选表：

    ------------------------
    |c_id | c_name | payout| 
    -----------------------
    |  1  | c_n1   | 100   |
    |  2  | c_n2   | 200   |
    |  3  | c_n3   | 300   |
    ------------------------

交易表：

--------------------------------
|transaction_id | c_id | valid | 
--------------------------------
|  12325sdfsf3  | 1    | 1     |
|  324ssggsgs3  | 1    | 1     |
|  f2fs33fs3sg  | 3    | 1     |
--------------------------------

标签： mysqlsql

解决方案

要获得所有交易的总支出，您需要执行加入。例如：

select
  count(*) as total_count,
  sum(c.payout) as total_payout
from transaction t
join campaign c on c.c_id = t.c_id

该SUM()函数会将payout列中的所有值相加。

推荐阅读