SQL substring group by

Here's one for ya. I'm baffled at this.

this query returns 2455 results
SELECT * FROM (
      SELECT	SUBSTRING(ORDERNO, 1, 6) AS ORDERNO,
		        SUM(DOLLARS) AS DOLLARS
      FROM TEMPORDR 
     GROUP BY ORDERNO 
  ) TBL1
WHERE DOLLARS >= 500.00
ORDER BY DOLLARS

Open in new window


and this query returns 2792 results:
SELECT * FROM (
SELECT	SUBSTRING(ORDERNO, 1, 6) AS ORDERNO
		,SUM(DOLLARS) AS DOLLARS
FROM TEMPORDR 
GROUP BY SUBSTRING(ORDERNO, 1, 6)
) TBL1
WHERE DOLLARS >= 500.00
ORDER BY DOLLARS

Open in new window


the order number look like this 368954*1, 368954*2, etc. The *1 denotes each level on an order so I want to ignore the suffix and group the order number part of the order number. i.e. 368954.

When I use the substring is should return less results because it should group the like terms. However it returns a lot more. Anyone know why?
LVL 2
David11011Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

cyberkiwiCommented:
The only reason that comes to mind now is that if you don't have your DOLLARS >= 500.0 condition, your statement may be true.  With the condition, what you see may be true if there are fewer records going purely by ORDERNO where the value will exceed 500.  When you group them by SUBSTRING, even though they have merged into fewer records, there will be more that match the condition >= 500
0

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
tim_csCommented:
Because of your WHERE DOLLARS >=500.  There are more records > 500 because more ORDERS are being grouped together.  
0
David11011Author Commented:
Holy Crap! I'm an idiot! thanks for your help teaching me some common sense lol.
0
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
Microsoft SQL Server 2005

From novice to tech pro — start learning today.