Click here to Skip to main content
15,845,681 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I create a salesDetails table, and I want to show the top 10 salesman who sales most(depending on sum of NetAmt from every bill) within given dates..

SQL
SalesDetails table Structure is :
bill_no -> Text
custName -> Text
slsDate -> text
slsmName -> text
vat -> text
totalAmt -> text
NetAmt -> text

I write the SQL query but its doesn't show the proper result, actually I'm new in SQL so please Help...
My Query is :-

SQL
select top 10 slsmName,sum(NetAmt) from salesDetails where slsDate between #15/3/2013# and #5/17/2013# and bilStatus=true order by totalAmt desc;


the proper result will be the Salesman name and total net-amount (net amount = sum of the netAmt between given dates), and I'm getting the error
Your query does not include the specified expression 'slsmName' as part of an aggregate function<br />


Thanks in advanced ...
Posted
Updated 17-May-13 5:51am
v2
Comments
Richard C Bishop 17-May-13 10:38am    
What is the proper result? What are you getting? More info.......
JayantaChatterjee 17-May-13 10:51am    
Please see the question I updated ...
Richard C Bishop 17-May-13 11:03am    
Put paranthesis around "10".
JayantaChatterjee 17-May-13 11:32am    
after I putted parenthesis around 10 :-
"The select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"
its shows error is here "slsmName"...

All the fields in your table are of type TEXT. Basic operations (e.g string operation) on TEXT data is not possible. You should should convert the column types such as TEXT to VARCHAR(250) for slsmName column, TEXT to FLOAT for NetAmt column and so on. Otherwise, DB can not calculate SUM (TEXT data). These datatype change operation may throw error. Let me know what are they. I shall let you the fix for them.

Anyway, you may use the following query to get intended result. But this will run once those type conversion are accomplished.

SQL
SELECT TOP 10 slsmName, SUM(NetAmt) AS TotalAmount FROM salesDetails WHERE slsDate BETWEEN #15/3/2013# AND #5/17/2013# GROUP BY slsmName ORDER BY TotalAmount DESC
 
Share this answer
 
Comments
JayantaChatterjee 18-May-13 11:18am    
Thanks a lottttttttt its works ..
Thank You..
JayantaChatterjee 18-May-13 11:42am    
But its ask "TotalAmount" value, when the query run..
debkumar@codeproject 19-May-13 12:06pm    
I didn't understand your query. Please explain.
SQL
select top 10 slsmName,sum(NetAmt) from salesDetails where slsDate between #15/3/2013# and #5/17/2013# and bilStatus=true Group by slsmName order by totalAmt desc;


See if this works for you.
 
Share this answer
 
Comments
JayantaChatterjee 17-May-13 11:29am    
same error shows .. :-(
Member 9581488 17-May-13 11:41am    
Can you please share your test Data?
JayantaChatterjee 17-May-13 11:50am    
Okay...
here is my test data, I give you only one row.
BI/12-13/000001
Jay Mukherjee
3/30/2013
Raj Roy
6.4
153.6
160
checked (last one is Yes/No type)..
Member 9581488 17-May-13 11:57am    
select top 10 slsmName,
sum(NetAmt)
from salesDetails
where slsDate between '03/15/2012'
and '5/17/2012'
and bilStatus=true
group by slsmName,totalAmt
order by totalAmt desc;

Try above query
JayantaChatterjee 17-May-13 12:00pm    
Its returns all rows from the tables ... :-(

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900