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 |
|
utkarshdesai
Starting Member
3 Posts |
 Posted - 2011-04-28 : 11:12:34
|
| I need to write a query in sql server 2000 where i have a database containing details of a sales team. I will try to explain my problem as simple as i can.I have a table with sales data. It contains details of sales of a product which my company manufactures. Now i am trying to find a way in which i can check the total sales of a customer. As the data in the server is a lot and the database is carrying data as old as 5 years so its not getting easy to find details of every customer one by one. Now is there a way in which i can add all the customers amount(Money they paid for service each time) and show it in one row rather then individually using there customer id(which is a primary key).For Example:If customer abc spend $1000 in total from the date they with us but they spend it in by coming in for 5 times till date so i want the result showing total amount spend ie $1000 and not all the 5 transactions which contain total of $1000.I am not that handy with sql so facing little difficulty.If need more clear view then can msg me and i will reply asap.Thanks Everyone. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-28 : 11:24:40
|
| Do you just mean thisSELECT Customer,SUM(amount) as TotalPaidFROM SalesDataGROUP BY CustomerJimEveryday I learn something that somebody else already knew |
 |
|
|
utkarshdesai
Starting Member
3 Posts |
Posted - 2011-04-28 : 11:51:37
|
| Thanks.It works as a charm for me. Just one more question, can i use more then two columns in the select query as i tried and it showed me an error like "Column 'Column_Extension' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." Thanks once again. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-28 : 12:07:13
|
| Just add that column to the GROUP BY clause, and it will give you a record for each column_extension that a customer might have.JimEveryday I learn something that somebody else already knew |
|
|
|
utkarshdesai
Starting Member
3 Posts |
Posted - 2011-04-28 : 12:40:08
|
| Hi Jim,It works.Thanks a lot. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-28 : 12:41:18
|
| You're Welcome. Welcome to SQL Team!JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
Adding rows.