SQL Count a number of rows

Question

I have the following:

SELECT nominees.personname, awards.name as awards, nominees.filmname 
FROM nominees, awards WHERE nominees.aid = awards.aid and nominees.personname is not 
NULL GROUP BY nominees.personname, awards.name, nominees.filmname 
ORDER BY nominees.personname;

This produces the following table:

"personname"    "awards"        "filmname"

"Ang Lee"       "director"      "Life of Pi"
"Anglelina J."  "actress"       "The Tourist"
"Ann Dowd"   "supporting actress"   "Compliance"
"Anne Hathaway" "actress"      "Love and Other Drugs"
"Anne Hathaway" "supporting actress"  "Les Misrables"
"Annette Bening"    "actress"   "The Kids Are All Right"
"Another Year"  "screenplay"    "Mike Leigh"
"A.R. Rahman"     "score"       "127 Hours"
"AR Rahman"       "score"       "127 Hours"
"Barbara Hershey"   "supporting actress"    "Black Swan"
"Ben Affleck"   "actor"         "Argo"
"Ben Affleck"   "director"      "Argo"

I'm attempting to get the set that contains only people who have been nominated for two separate awards for the same movie. In this particular table that is only 'Ben Affleck'.

The results should be

    "personname"    "awards"        "filmname"

    "Ben Affleck"   "actor"         "Argo"
    "Ben Affleck"   "director"      "Argo"

but I can't seem to get that, I've tried using HAVING and some sort of count method, but haven't been able to get that working. Any help is appreciated.

can you give some more info about the table structure of nominees and awards? — Saurabh
– Saurabh, Commented Mar 5, 2013 at 20:41

Ihor Romanchenko · Accepted Answer · 2013-03-05 20:47:42Z

This is simple aggregation query. To get the person do:

select personname, filmname
from (SELECT nominees.personname, awards.name as awards, nominees.filmname 
      FROM nominees, awards WHERE nominees.aid = awards.aid and nominees.personname is not NULL
      GROUP BY nominees.personname, awards.name, nominees.filmname 
     ) t
 group by personname, filmname
 having count(*) > 1

You can phrase this in terms of the original data and get the list in one row:

SELECT nominees.personname, nominees.filmname, array_agg(awards.name) as listOfaward
FROM nominees join
     awards
     on nominees.aid = awards.aid
where nominees.personname is not NULL
GROUP BY nominees.personname, nominees.filmname 
having COUNT(distinct  awards.name) > 1

To get a separate row for each award, you can join back to the original data:

select personname, filmname, a.name
from (SELECT nominees.personname, nominees.filmname, array_agg(awards.name) as listOfaward
      FROM nominees join
           awards
           on nominees.aid = awards.aid
      where nominees.personname is not NULL
      GROUP BY nominees.personname, nominees.filmname 
      having COUNT(distinct  awards.name) > 1
     ) l join
     nominees n
     on n.personname = l.personname and n.filmname = l.filmname join
     awards a
     on n.aid = a.aid

Another way - COUNT(distinct awards.name) as window function
This third suggestion is exactly what I was looking for, I had done the first one, but couldn't find a good way to get each award in a separate row. Thank you.

Oli · Accepted Answer · 2013-03-05 20:42:48Z

0

I think it should work like this:

SELECT nominees.personname, awards.name as awards, nominees.filmname, count(distinct  awards.name) as number_awards
FROM nominees, awards WHERE nominees.aid = awards.aid and nominees.personname is not 
NULL 
GROUP BY nominees.personname, awards.name, nominees.filmname 
HAVING number_awards > 1
ORDER BY nominees.personname;

answered Mar 5, 2013 at 20:42

Oli

2,4943 gold badges29 silver badges48 bronze badges

1 Comment

Ihor Romanchenko Over a year ago

It wont work. count(distinct awards.name) will always be 1 because you are grouping by awards.name

Collectives™ on Stack Overflow

SQL Count a number of rows

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related