不同的员工姓名购买金额的总和？

Question

我创建了这个查询。它返回超过 35000 条员工及其一年内购买记录。我试图让查询仅返回员工（姓名）的 1 个实例和总购买金额，而不是单独的记录。这可能吗？

SELECT

distinct employee.employee_no,

sum(employee_purchase.purchase_amount) as 'Purchase Amount',

--employee.employee_no,

employee.employee_first_name,

employee.employee_last_name,

employee.home_store_no,

employee_purchase.transaction_date,

employee_purchase.employee_discount_amount


FROM

employee


INNER JOIN

employee_purchase ON employee.employee_no =

employee_purchase.employee_no



where

transaction_date between ('2010-06-30 00:00:00') and ('2011-04-26 00:00:00')


group by employee.employee_no, employee.employee_first_name, employee.employee_last_name,

employee.home_store_no,

employee_purchase.transaction_date,

employee_purchase.employee_discount_amount


order by 1 

Answer 1

摆脱你的 DISTINCT，并删除交易数据。想想看：如果您想对所有交易进行求和，您就不可能同时获得有关单个交易的信息。

SELECT
  employee.employee_no,
  sum(employee_purchase.purchase_amount) as 'Purchase Amount',
  employee.employee_first_name,
  employee.employee_last_name,
  employee.home_store_no
FROM
  employee
INNER JOIN employee_purchase ON employee.employee_no = employee_purchase.employee_no
where
transaction_date between ('2010-06-30 00:00:00') and ('2011-04-26 00:00:00')
group by 
  employee.employee_no, employee.employee_first_name, employee.employee_last_name, employee.home_store_no
order by 1