CODEWITHSUNDEEP
sql-server-2008
Hemant Kumar
Hemant Kumar

Reputation: 4611

Distinct employee name sum of purchase amount?

I have this query I have created. It returns over 35000+ records for employees and their purchases for 1 year. I am trying to have the query return only 1 instance of the employee (name) and the total purchase amount instead of indivdual records. Is this possible?

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

Upvotes: 0

Views: 206

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30815

Get rid of your DISTINCT, and remove the transaction data. Think about it: if you want to sum over all transactions, you cannot also have information about a single transaction.

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

Upvotes: 1

Related Questions