How to write this SQL query? (Help Needed)

Question

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 17-May-13 4:37am

JayantaChatterjee

Updated 17-May-13 4:51am

v2

Add a Solution

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"...

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

debkumar@codeproject · Answer 1 · 2013-05-17T09:21:00

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

Member 9581488 · Answer 2 · 2013-05-17T05:19:00

Solution 1

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.

Posted 17-May-13 5:19am

Member 9581488

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 ... :-(

Member 9581488 17-May-13 12:02pm

Change dates!

JayantaChatterjee 17-May-13 12:06pm

I changed dates 2012 to 2013 but same result(all rows are display) nothing grouped ...

Member 9581488 17-May-13 12:08pm

are you getting any error or its returning all rows?

Member 9581488 17-May-13 12:13pm

http://sqlfiddle.com/#!2/3ea58/5 check this link and see if its returning your desired result in output