Postgres script an easier way

Question

I'm wondering if there is an easier way to write the query below. In a nutshell the outcome of the query is to sum all the oscar awards the movie Titanic has won and the awards are contained in different tables. This script works but seems convoluted.

SELECT title, production_year, 
COUNT(*) + (crew.c + director.c + writer.c + actor.c) AS Oscars
FROM movie_award, 
(SELECT COUNT(*) AS c
    FROM crew_award 
    WHERE result = 'won'
    AND title = 'Titanic'
    AND award_name = 'Oscar') AS crew,
(SELECT COUNT(*) AS c
    FROM director_award
    WHERE result = 'won'
    AND title = 'Titanic'
    AND award_name = 'Oscar') AS director,
(SELECT COUNT(*) AS c
    FROM writer_award
    WHERE result = 'won'
    AND title = 'Titanic'
    AND award_name = 'Oscar') AS writer,
(SELECT COUNT(*) AS c
    FROM actor_award
    WHERE result = 'won'
    AND title = 'Titanic'
    AND award_name = 'Oscar') AS actor
WHERE result = 'won'
AND title = 'Titanic'
AND award_name = 'Oscar'
GROUP BY crew.c, director.c, writer.c, actor.c, title, production_year;

It'd be rather more useful if you'd show the tables and data. Was this downloaded from somewhere? Can you create a summary sqlfiddle.com? — Craig Ringer
– Craig Ringer, Commented Oct 1, 2014 at 7:57
No I wrote it and it's very easy to see the attributes that are needed from the script. — Bob Jane
– Bob Jane, Commented Oct 1, 2014 at 8:11

Joachim Isaksson · Accepted Answer · 2014-10-01 08:46:12Z

1

A bit hard to give a "run as is" answer without seeing the schema, but you should be able to use UNION ALL to simplify things somewhat, something like;

SELECT title, production_year, COUNT(*) Oscars
FROM ( 
  SELECT title, production_year, result, award_name FROM movie_award
  UNION ALL
  SELECT title, production_year, result, award_name FROM crew_award
  UNION ALL
  SELECT title, production_year, result, award_name FROM director_award
  UNION ALL
  SELECT title, production_year, result, award_name FROM writer_award
  UNION ALL
  SELECT title, production_year, result, award_name FROM actor_award
)z
WHERE result = 'won' AND title = 'Titanic' AND award_name = 'Oscar'
GROUP BY title, production_year;

answered Oct 1, 2014 at 8:46

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

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

Collectives™ on Stack Overflow

Postgres script an easier way

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related