Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bobcoop
Starting Member
2 Posts |
 Posted - 2007-04-30 : 11:20:01
|
| I have been toiling over getting this query to run properly for a while now and finally had to turn to the community for help. We are trying to develop a query that will show us an idea of customer loyalty at the store. It basically takes the transactions for a specific user from one table, and matches that customer ID to the customer table so we can also select names and phone numbers. The query runs great, but it shows every single transaction that a customer has done between our specified time range. We want to be able to have the query add each transaction and total it for us so we have just one customerID with one total instead of say one customerID with 19 transactions. Any ideas? Our current query is below:select customer.accountnumber, customer.firstname, customer.lastname, xaction_total From Customer, xaction where xaction_time between '01/1/2006' and '12/31/2006'and Customer.ID = xaction.CustomerID and customer.accountnumber not like 'X%'order by customer.accountnumber desc |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-30 : 11:22:08
|
[code]select customer.accountnumber, customer.firstname, customer.lastname, SUM(xaction_total)From Customer INNER JOIN xaction ON Customer.ID = xaction.CustomerIDwhere xaction_time between '01/1/2006' and '12/31/2006'and customer.accountnumber not like 'X%'GROUP BY customer.accountnumber, customer.firstname, customer.lastnameorder by customer.accountnumber desc[/code]
KH |
 |
|
|
Bobcoop
Starting Member
2 Posts |
Posted - 2007-04-30 : 12:02:24
|
| Thanks for the quick reply! This worked great! Thanks a bunch. The syntax on the group by statement was throwing us off. Again, I appreciate the help. |
|
|
|
|
|
|
|
|
Question regarding summation fo two tables.