Prev: count unique nodes
Next: Script-Out Full Text Search?
From: larryg003 on 11 Jun 2010 13:04
Hi guys, i just joined SQLMONSTER and was wondering if you guys can help me
with this problem...

I have 10 columns and 8,000,000 rows which have stock ticker names and
exchange tickers-like the following:
[Column A]
AQR.L
GDY.LS
NBAE.LS
(blank)
0023CA.S
FOR.L
AQR.KZ
...
...
How do I find the number of unique ticker names in my database and disregard
the .L, .LS, .S, .L, .KZ endings (because I am looking for the unique tickers,
not the exchange). I.E., I want
the values counted to be only AQR, GDY, NBAE, 0023CA, FOR or 5 values (I
don't want AQR to be counted twice because it is listed on two separate
exchanges).

I tried:

select
left([Column A],charindex('.',[Column A])-1),
count(*) as cnt
from your_table
group by left([Column A],charindex('.',[Column A])-1)
where Column_A is not null

and I got an error message.

Any thoughts? I am using SQL Management Studio

THANK YOU SO MUCH!

From: Tom Cooper on 11 Jun 2010 13:41
It's always best when you are asking a question about an error you recieved
to tell us what the error was. However, when you do a left(), you must make
sure the length is >= 0. But if you don't have a "." in some row in your
column, then the charindex returns 0 for that row, you subtract 1 getting -1
and use that value for the length in your left() function which causes an
error. I think you want something like:

Declare @Test Table([Column A] varchar(20));
Insert @Test ([Column A])
Select 'AQR.L'
Union All Select 'GDY.LS'
Union All Select 'NBAE.LS'
Union All Select ''
Union All Select '0023CA.S'
Union All Select 'FOR.L'
Union All Select 'AQR.KZ';

With cte As
(select left(Coalesce([Column A], ''), charindex('.',Coalesce([Column
A],'')+'.') - 1) As MyString
from @Test)
select MyString
from cte
where MyString <> ''
group by MyString;

Tom
"larryg003" <u60820(a)uwe> wrote in message news:a960254d501f1(a)uwe...
> Hi guys, i just joined SQLMONSTER and was wondering if you guys can help
> me
> with this problem...
>
> I have 10 columns and 8,000,000 rows which have stock ticker names and
> exchange tickers-like the following:
> [Column A]
> AQR.L
> GDY.LS
> NBAE.LS
> (blank)
> 0023CA.S
> FOR.L
> AQR.KZ
> ..
> ..
> How do I find the number of unique ticker names in my database and
> disregard
> the .L, .LS, .S, .L, .KZ endings (because I am looking for the unique
> tickers,
> not the exchange). I.E., I want
> the values counted to be only AQR, GDY, NBAE, 0023CA, FOR or 5 values (I
> don't want AQR to be counted twice because it is listed on two separate
> exchanges).
>
> I tried:
>
> select
> left([Column A],charindex('.',[Column A])-1),
> count(*) as cnt
> from your_table
> group by left([Column A],charindex('.',[Column A])-1)
> where Column_A is not null
>
> and I got an error message.
>
> Any thoughts? I am using SQL Management Studio
>
> THANK YOU SO MUCH!
>

From: larryg003 on 11 Jun 2010 14:52
Tom, thank you for answering my post, but I don't know all of the tickers so
I can't use the union function.

I just want to count the distinct ID's disregarding the endings: .kz, .kl, .L,
.S, etc.

Any ideas would be greatly appreciated,

thank you!

From: Tom Cooper on 11 Jun 2010 15:54
The DECLARE and INSERT statement (with the SELECTs and UNIONs) is just to
create a sample table with sample data. The actual query begins with the
WITH cte AS
line. Rereading your original post, I may have misinterped it. Maybe you
just want the count (that is you want the query to return one row with the
value 5)? That would be

With cte As
(select left(Coalesce([Column A], ''), charindex('.',Coalesce([Column
A],'')+'.') - 1) As MyString
from @Test)
select Count(Distinct MyString)
from cte
where MyString <> '';

In case that is not what you want, the best way to make it clear what you
want is to give us sample data (in the form of CREATE TABLE or DECLARE
<name> TABLE and INSERT statements and tell us exactly what you want
returned in the result set.

Tom

"larryg003" <u60820(a)uwe> wrote in message news:a96117b322282(a)uwe...
> Tom, thank you for answering my post, but I don't know all of the tickers
> so
> I can't use the union function.
>
> I just want to count the distinct ID's disregarding the endings: .kz, .kl,
> .L,
> S, etc.
>
> Any ideas would be greatly appreciated,
>
> thank you!
>

From: larryg003 on 11 Jun 2010 16:46
Thanks Tom,

I am looking to determine the number of individuals tickers (Counted)- i
don't care about what exchange they are traded on.

also what does the @Test line mean? Is it supposed to be @table_name

 |  Next  |  Last
Pages: 1 2
Prev: count unique nodes
Next: Script-Out Full Text Search?