min query issues

SELECT  COUNT (*) from SALARIES  WHERE salary>(SELECT  MIN (Salary ) from SALARIES )


somet reason above i am getting wrong count
instead of 6 i am getting 15

please advise on how to optimize and make it better
LVL 7
gudii9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> instead of 6 i am getting 15

Query seems correct and no need for further optimization..
Can you kindly explain with some test data to show why it should return 6 instead of 15 so that we can help modify the query accordingly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
min(salary) is 1 record (or more records with same salary)

it's returning everything except the first record
awking00Information Technology SpecialistCommented:
Please post the salary values that you queried.
Jim HornSQL Server Data DudeCommented:
Agree with the above experts.  Keep in mind that experts here cannot connect to your data sources and run queries, so we are entirely dependent on what you provide us in the question to provide an answer, and that includes sample data.

Otherwise why 15 is being returned instead of 6 ... no idea.

Off of top of my head though (as Geert states above) if you are filtering by the minimum value of Salary then I would expect the return set to have the same number of rows in the table itself minus those rows where the salary = minimum value, and minus any NULLs.
slightwv (䄆 Netminder) Commented:
Also agree that we cannot help without seeing the data.

As I a posted in your MAX question:  If you think you need to query the table twice, rethink it.

This should return the same results and only access the table once:
SELECT  COUNT (*) from
(
	select salary, min(salary) over () min_salary from tab1
) salaries
WHERE salary> min_salary
/

Open in new window


Without the data you have, I cannot say if it will return 6 or 15 rows.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.