mysql - 同时选择连接表行的计数和计数总和

Question

我有 2 个表用户和交易

我想选择用户交易次数和交易总和。

我的查询看起来像

SELECT users.id,
    COUNT(transactions.amount) AS total_transactions,
    SUM(transactions.amount) AS total_amount
FROM 
    users 
LEFT JOIN transactions ON ( (transactions.user_id = users.id) AND (transactions.status=3) ) 
GROUP BY users.id 
HAVING total_transactions = X

效果不太好。

我的问题是如何同时使用 count 和 sum？

我应该做什么？

这是我的完整查询

这是我的完整查询:

SELECT 
    users.id, users.gender, users.phone, users.email, 
    COUNT( DISTINCT(transactions.id)) AS total_transactions, 
    SUM( transactions.amount) AS total_amount, 
    users.verification_required, user_details.optin_mail_sent, users.last_login, activities.updated_at AS last_activity 
FROM 
    users LEFT JOIN transactions ON ( (transactions.user_id = users.id) AND (transactions.status=3) ) 
LEFT JOIN 
    user_details ON (user_details.user_id = users.id) 
LEFT JOIN 
    activities ON (activities.user_id = users.id) 
WHERE (DATE(users.last_login) >= DATE_SUB( CURRENT_DATE, INTERVAL 3 MONTH)) AND (DATE(users.last_login) <= DATE_SUB( CURRENT_DATE, INTERVAL 1 MONTH)) 
    AND (users.gender='female') 
    AND (users.verification_required='1') 
    AND (users.unverified=0) 
    AND (users.active=1) 
    AND (users.gender <> '') 
GROUP BY users.id 
HAVING total_transactions = 3 
ORDER BY activities.updated_at DESC

Answer 1

也许您需要聚合结果，然后将结果连接到表 users以及其余表格:

SELECT 
  u.id, u.gender, u.phone, u.email, 
  t.total_transactions, 
  t.total_amount, 
  u.verification_required, ud.optin_mail_sent, u.last_login, a.updated_at AS last_activity 
FROM users AS u 
LEFT JOIN (
  SELECT user_id, 
  COUNT(DISTINCT id) AS total_transactions,
  SUM(amount) AS total_amount
  FROM transactions
  GROUP BY user_id
  HAVING total_transactions = 3
) AS t ON (t.user_id = u.id) AND (t.status=3) 
LEFT JOIN user_details AS ud ON ud.user_id = u.id 
LEFT JOIN activities AS a ON a.user_id = u.id 
WHERE 
  (DATE(u.last_login) >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)) 
  AND 
  (DATE(u.last_login) <= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)) 
  AND (u.gender='female') 
  AND (u.verification_required='1') 
  AND (u.unverified=0) 
  AND (u.active=1)
ORDER BY a.updated_at DESC

我对表使用了别名来缩短代码并使其更具可读性。
我还删除了条件 users.gender<>''因为已经存在条件 users.gender='female' 。