Counting the records

Question

Post reply

Counting the records

raj-247939

SSCertifiable

Points: 5999
More actions
January 3, 2006 at 1:52 am

#166389

Hi All ,

I need to count the records in the table but the duplicate record should be counted once.

Ex:
A                       B
1                       hhhhh
1                       mmmm
1                       kjkjj
2                        332
3                       dsd
3                       asdfsa

Now if we count the record the result should be count=3 not count=6
from
Killer

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

shubhada Valued Member Points: 65 More actions · Answer 1

Try this

select distinct ID from table_name

query returns :

A

1

2

3

RyanRandall SSChampion Points: 13623 More actions · Answer 2

Or this:

select count(distinct A) from table_name

It returns: 3

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

raj-247939 SSCertifiable Points: 5999 More actions · Answer 3

Sorry not to mention that the ERP application(Naviosion Axapta) we are using do not support distinct function.

so do we have another way of doing this

from

killer

Phil Parkin SSC Guru Points: 246944 More actions · Answer 4

Could you create a distinct view and run your query against that?

If not, please mention what other restrictions you have to work round.

Regards

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

RyanRandall SSChampion Points: 13623 More actions · Answer 5

Erm, how about:

select count(*) from (select A from table_name group by A) a

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

raj-247939 SSCertifiable Points: 5999 More actions · Answer 6

Hi Ryan,

Thanx It worked like magic.

Thanx to all of u.

from

Killer