Count + Distinct SQL Query

Question

I have a research table like

researcher    award

 person1      award1
 person1      award2
 person2      award3

What I want to get is to count the award based on researcher but it researcher shouldnt be repeated. So in this example. The result should be

Coz award1 and award2 is the same person + award3 which is a different person.

I already tried

SELECT count(award) from research where researcher=(select distinct(researcher) from researcher)

But it says

ERROR:  more than one row returned by a subquery used as an expression

So any alternative solution or changes?

SELECT researcher, count(award) FROM research GROUP BY researcher? — Anthony Grist
– Anthony Grist, Commented Mar 12, 2014 at 16:21
Are you trying to count the people or the awards? In other words, is the result "2" because person 1 has 2 awards, or because there are 2 people that have awards? — mayabelle
– mayabelle, Commented Mar 12, 2014 at 16:21

Hogan · Accepted Answer · 2014-03-12 16:21:18Z

2

This will give you researcher and count

select researcher, count(*) as c
from table
group by researcher

maybe you only want awarded ones?

select researcher, count(*) as c
from table
where award is not null
group by researcher

answered Mar 12, 2014 at 16:21

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Saher Ahwal · Accepted Answer · 2014-03-12 18:55:06Z

0

select count(*) from (select researcher, count(*) 
from MyTable
group by researcher) as tempTable;

edited Mar 12, 2014 at 18:55

answered Mar 12, 2014 at 16:23

Saher Ahwal

9,27733 gold badges91 silver badges156 bronze badges

6 Comments

Kino Over a year ago

why is that there is 'as cnt'? can't we just erase it? I tried erasing 'as tempTable' but it says subquery should have a name.

Kino Over a year ago

And oh yes, where can I put 'where award is not null'?

Hogan Over a year ago

This query makes no sense

Saher Ahwal Over a year ago

removed as cnt since there is no need.

Saher Ahwal Over a year ago

@Hogan this exactly returns what the OP requested. It returns the number of awards without double counting the researchers. It counts the rows of the group by query...

|

xdazz · Accepted Answer · 2014-03-12 16:22:38Z

0

You could just do a group by and count the distinct award.

SELECT count(distinct award) from research group by researcher

answered Mar 12, 2014 at 16:22

xdazz

161k38 gold badges255 silver badges278 bronze badges

Comments

Code Maverick · Accepted Answer · 2014-03-12 16:48:29Z

0

You just need to use GROUP BY:

SELECT    COUNT( DISTINCT Award ) AS awardCount 

FROM      Research 

GROUP BY  Researcher

edited Mar 12, 2014 at 16:48

Code Maverick

20.5k12 gold badges65 silver badges117 bronze badges

answered Mar 12, 2014 at 16:23

xero

4,3291 gold badge27 silver badges40 bronze badges

Collectives™ on Stack Overflow

Count + Distinct SQL Query

4 Answers 4

Comments

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related