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?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.