时间戳

首页 > 解决方案 > 如何通过 SELECT 子查询聚合进行分组？

mysql - 如何通过 SELECT 子查询聚合进行分组？

问题描述

第一次来这里，希望得到帮助。（MySQL）我尝试在 SELECT 语句中使用子查询，但是当我 GROUP BY 时，子查询的单个聚合值输出只会为表中的所有行生成一个相同的值。这意味着它们没有分组，对吧？我离做到这一点有多近？谢谢

SELECT 
  c.name, ca.name, DATE_FORMAT(sp.created,'%Y%m') AS yr_month,
  ss.signup_source, count(sp.seller_profile_id) AS No_seller_profiles,    
  (SELECT SUM(seller_invoice.gbp_value)/100 
     FROM seller_invoice JOIN seller_profile 
       ON seller_invoice.seller_profile_id = seller_profile.seller_profile_id 
     WHERE seller_invoice.created BETWEEN seller_profile.created AND ADDDATE(seller_profile.created, INTERVAL 30 DAY)),
  (SELECT count(project_response.project_response_id) 
     FROM project_response JOIN seller_profile 
       ON project_response.seller_profile_id = seller_profile.seller_profile_id 
     WHERE project_response.created BETWEEN seller_profile.created AND ADDDATE(seller_profile.created, INTERVAL 30 DAY) AND project_response.is_visible_to_seller = 1)
FROM seller_profile AS sp 
JOIN country AS c ON sp.country_id = c.country_id
JOIN seller_category AS sc ON sp.seller_profile_id = sc.seller_profile_id
JOIN category AS ca ON sc.category_id = ca.category_id
JOIN seller_signup_source AS ss ON sp.seller_profile_id = ss.seller_profile_id
WHERE sp.created BETWEEN '2018-11-01' AND '2018-12-31'
GROUP BY 1,2,3,4;

标签： mysqlselectgroup-bysubquery

解决方案

推荐阅读