asked on

Count duplicate cells in a coulmn only once sql statement

I want a select query in which rows should be counted only once based on column value.

Table

AppName            DBName            DBVersion

A1            db1            Microsoft
A2            db2            Microsoft
A3            db1            Microsoft
A4            db1            Microsoft
A5            db4            Oracle
A6            db5            Microsoft
A7            db2            Microsoft
A8            db3            Microsoft
A9            db4            Oracle
A10 db8 Oracle

In this case the result should count DBName only once.

select count(*) from Table group by DBName where DBVersion='Microsoft'

=4

and

select count(*) from Table group by DBName where DBVersion='Oracle'
=2

Can this be done.

Also, can the rows be shown instead of count?

Thanks

Éric Moreau

to get the count, do :

select count(distinct dbname) from Table where DBVersion='Microsoft'

Open in new window

>>Also, can the rows be shown instead of count?

select distinct dbname, dbversion from Table where DBVersion='Microsoft'

Open in new window

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DevSupport

ASKER

@Éric Moreau I tried the distinct statement but it gives me duplicate values in the result