Group by problem!

I need to use Group By on a text column that contain numeric data in
different format.
For example, column "terminalID" can contain "0123" and '123' pointing to
the same terminal.
Group by treats these as 2 different groups.
I used CAST (terminalID as INT) as 'terminalID' in SELECT but still got the
same problem since I cannot use CAST in Group By clause.
Any help is greatly appreciated!
Bill
select CAST(terminalID as INT) as terminalID
from table A
group by terminalID> I used CAST (terminalID as INT) as 'terminalID' in SELECT but still got
> the same problem since I cannot use CAST in Group By clause.
CAST is allowed in a GROUP BY clause. The example below should work,
assuming terminalID is always numeric.
SELECT CAST(terminalID AS int) AS terminalID
FROM MyTable
GROUP BY CAST(terminalID AS int)
Hope this helps.
Dan Guzman
SQL Server MVP
"Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:O3Ud$mBBGHA.1028@.TK2MSFTNGP11.phx.gbl...
>I need to use Group By on a text column that contain numeric data in
>different format.
> For example, column "terminalID" can contain "0123" and '123' pointing to
> the same terminal.
> Group by treats these as 2 different groups.
> I used CAST (terminalID as INT) as 'terminalID' in SELECT but still got
> the same problem since I cannot use CAST in Group By clause.
> Any help is greatly appreciated!
> Bill
>
> select CAST(terminalID as INT) as terminalID
> from table A
> group by terminalID
>
>|||>> I need to use Group By on a text column that contain numeric data in diff
erent format. <<
Well, that is realllllly screwed up! In the RDBMS model, unlike 1950's
COBOL, there is no formatting in the database. We have abstract data
types. Look at the possible Numeric data types in any basic book on
SQL.
Gee, those are strings and not numeric at all! This is sooo basic.
1) Find the moron that did this schema and kill him. It will greatly
improve data quality.
2) Now, decide if this column is a string or a numeric; change the
table to reflect this decision; add a CHECK() constraint mto enforce
the correct format. Go thru all of your code and clean it up.
3) Read any book on SQL programming.|||Sounds like it is possible that the text column has 0s for padding when it
is created. Prior to Casting perhaps you could use a substring or trim
function to clean up the data. You might have to put the data in a temp
table are requery it so that you query the clean stuff.
Keith
"Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:O3Ud$mBBGHA.1028@.TK2MSFTNGP11.phx.gbl...
>I need to use Group By on a text column that contain numeric data in
>different format.
> For example, column "terminalID" can contain "0123" and '123' pointing to
> the same terminal.
> Group by treats these as 2 different groups.
> I used CAST (terminalID as INT) as 'terminalID' in SELECT but still got
> the same problem since I cannot use CAST in Group By clause.
> Any help is greatly appreciated!
> Bill
>
> select CAST(terminalID as INT) as terminalID
> from table A
> group by terminalID
>
>|||Thank you all.
Dan's statement below solved the problem.
Celko's comments are seriously considered
Bill
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uRKfgtBBGHA.240@.TK2MSFTNGP11.phx.gbl...
> CAST is allowed in a GROUP BY clause. The example below should work,
> assuming terminalID is always numeric.
> SELECT CAST(terminalID AS int) AS terminalID
> FROM MyTable
> GROUP BY CAST(terminalID AS int)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
> news:O3Ud$mBBGHA.1028@.TK2MSFTNGP11.phx.gbl...
>