CODEWITHSUNDEEP
sqlpostgresqlcount
Night Train
Night Train

Reputation: 781

SQL Count a number of rows

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.

Upvotes: 0

Views: 211

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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

Upvotes: 3

Oli
Oli

Reputation: 2452

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;

Upvotes: 0

Related Questions