Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Counting without duplicates

Tags:

sql

I have a table such as the following

 FK_OrgId , FK_UserId
 3 , 74
 1 , 74
 1 , 74
 3 , 4
 4 , 5
 1 , 5

I'm trying to count FK_OrgId but I have to eliminate duplicates. So, I want a result like

 FK_OrgId, count
 3 , 2
 1 , 2
 4 , 1

Any suggestions?

like image 963
Marc Avatar asked Apr 21 '13 17:04

Marc


People also ask

Can Excel count unique values?

In Excel, functions are always available to solve any operations. In this case, you can use a combination of SUM, IF and COUNTIF functions to count unique values in Excel. To count unique values, enter the formula =SUM(IF(COUNTIF(range, range)=1,1,0)) in the desired cell.

How do you exclude duplicates in Excel?

In Excel, there are several ways to filter for unique values—or remove duplicate values: To filter for unique values, click Data > Sort & Filter > Advanced. To remove duplicate values, click Data > Data Tools > Remove Duplicates.


2 Answers

The key here is to use DISTINCT inside COUNT() so it will only count unique values.

SELECT  FK_OrgId, COUNT(DISTINCT FK_UserId)
FROM    TableName
GROUP   BY FK_OrgId
  • SQLFiddle Demo

OUTPUT

╔══════════╦════════════╗
║ FK_ORGID ║ TOTALCOUNT ║
╠══════════╬════════════╣
║        1 ║          2 ║
║        3 ║          2 ║
║        4 ║          1 ║
╚══════════╩════════════╝
like image 100
John Woo Avatar answered Oct 15 '22 00:10

John Woo


you should use distinct key word so as to avoid dupplicates

select  t.FK_OrgId, count(distinct t.FK_UserId)
from    TableName as t
group by t.FK_OrgId
like image 39
Stephane Rolland Avatar answered Oct 15 '22 02:10

Stephane Rolland