How to use DCount function

I am new to SQL Server. I want to use the DCount function ( Iam trying to
enter the occurence of a particular field in another field) in a Update
query but it gives me an error message. Is there a way out? Thanks in advanc
e.There is no Dcount function in T-SQL. Can you give us an example of what
you need?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:A4EC3B50-E46E-45FF-A240-2B03948CFA62@.microsoft.com...
I am new to SQL Server. I want to use the DCount function ( Iam trying to
enter the occurence of a particular field in another field) in a Update
query but it gives me an error message. Is there a way out? Thanks in
advance.|||I have a table with 3 fields. The first is a number that is the primary key,
second is a text field and the third one is empty. The text in the second
field can have duplicates. I have to populate the third field by the number
of occurences of the text in the second field. I am typing an example below:
1 AAA
2 BBB
3 CCC
4 DDD
5 AAA
6 CCC
7 AAA
8 EEE
This should be like:
1 AAA 1
2 BBB 1
3 CCC 1
4 DDD 1
5 AAA 2
6 CCC 2
7 AAA 3
8 EEE 1
I hope I have made myself clear. Thanks
"Tom Moreau" wrote:

> There is no Dcount function in T-SQL. Can you give us an example of what
> you need?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:A4EC3B50-E46E-45FF-A240-2B03948CFA62@.microsoft.com...
> I am new to SQL Server. I want to use the DCount function ( Iam trying to
> enter the occurence of a particular field in another field) in a Update
> query but it gives me an error message. Is there a way out? Thanks in
> advance.
>|||Hi
CREATE TABLE #Test
(
col1 INT NOT NULL PRIMARY KEY,
col2 CHAR(3) NOT NULL
)
INSERT INTO #Test VALUES (1,'AAA')
INSERT INTO #Test VALUES (2,'BBB')
INSERT INTO #Test VALUES (3,'CCC')
INSERT INTO #Test VALUES (4,'DDD')
INSERT INTO #Test VALUES (5,'AAA')
INSERT INTO #Test VALUES (6,'CCC')
INSERT INTO #Test VALUES (7,'AAA')
INSERT INTO #Test VALUES (8,'EEE')
SELECT col1,col2,(SELECT COUNT(*)
FROM #Test t WHERE t.col1<=#Test.col1
AND t.col2=#Test.col2) FROM #Test
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5DD0D169-ED50-47CB-9DC6-BC7A621D821F@.microsoft.com...
> I have a table with 3 fields. The first is a number that is the primary
key,
> second is a text field and the third one is empty. The text in the second
> field can have duplicates. I have to populate the third field by the
number
> of occurences of the text in the second field. I am typing an example
below:
> 1 AAA
> 2 BBB
> 3 CCC
> 4 DDD
> 5 AAA
> 6 CCC
> 7 AAA
> 8 EEE
> This should be like:
> 1 AAA 1
> 2 BBB 1
> 3 CCC 1
> 4 DDD 1
> 5 AAA 2
> 6 CCC 2
> 7 AAA 3
> 8 EEE 1
> I hope I have made myself clear. Thanks
> "Tom Moreau" wrote:
>
what
to|||Check it out TableName Dworld a identity field 2nd is text field and 3rd one
occurence (ofcourse poor naming convention )
UPDATE D
SET D.TXTCount = cnt
from Dworld D INNER JOIN (select d.a as k, count(d.text) as cnt
from dworld d inner join dworld d1 on d.text = d1.text and d.a >= d1.a
group by d.a) as p on d.a = p.k
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5DD0D169-ED50-47CB-9DC6-BC7A621D821F@.microsoft.com...
> I have a table with 3 fields. The first is a number that is the primary
key,
> second is a text field and the third one is empty. The text in the second
> field can have duplicates. I have to populate the third field by the
number
> of occurences of the text in the second field. I am typing an example
below:
> 1 AAA
> 2 BBB
> 3 CCC
> 4 DDD
> 5 AAA
> 6 CCC
> 7 AAA
> 8 EEE
> This should be like:
> 1 AAA 1
> 2 BBB 1
> 3 CCC 1
> 4 DDD 1
> 5 AAA 2
> 6 CCC 2
> 7 AAA 3
> 8 EEE 1
> I hope I have made myself clear. Thanks
> "Tom Moreau" wrote:
>
what
to|||Thanks eveyone for your support.
"Yogesh parashar" wrote:

> Check it out TableName Dworld a identity field 2nd is text field and 3rd o
ne
> occurence (ofcourse poor naming convention )
> UPDATE D
> SET D.TXTCount = cnt
> from Dworld D INNER JOIN (select d.a as k, count(d.text) as cnt
> from dworld d inner join dworld d1 on d.text = d1.text and d.a >= d1.a
> group by d.a) as p on d.a = p.k
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5DD0D169-ED50-47CB-9DC6-BC7A621D821F@.microsoft.com...
> key,
> number
> below:
> what
> to
>
>