David11011
asked on
SQL substring group by
Here's one for ya. I'm baffled at this.
this query returns 2455 results
and this query returns 2792 results:
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?
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
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
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER