Mysql Join, Count and Sum?

campaign table are the item for sale and on the transaction table are the item sold. I want to count the transaction_id that are made transaction table and sum the total of sold item.. but the table are different..

Here is what I tried:

$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."]";

campaign table:

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

transactions table:

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

Answer

To get the total payout for all transactions you need to perform a join. For example:

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

The SUM() function will add up all values from the payout column.

Advertisement

Answer