Getting number of rows of a query as output when COUNT(*) can't be used-CodePudding

I have this query that returns 13 rows.

SELECT DISTINCT title,year FROM MovieAwards WHERE EXISTS (SELECT DISTINCT * FROM Movies WHERE MovieAwards.title = Movies.title AND Movies.year = MovieAwards.year AND Movies.year >= 2000 AND Movies.year <= 2010 AND MovieAwards.result='won');

Now i need to use the number of rows of this query as a float for other queries. I am aware that i can use THIS AND AS to store the number of rows in some temporary variable. But i can't find a single way to modify the first query to output 13.

If i do SELECT COUNT(*) FROM MovieAwards WHERE EXISTS IN (the code above)

i get a very different number as title,year is not the key of MovieAwards.

I also tried to replace title,year with count(title,year) but i'm guessing that isn't allowed.

I can't think of anything else to try. Any help would be apretiated.

CodePudding user response：

You can select the count of some literal to get a count for your selection. Please refer below.

SELECT COUNT(1) FROM (SELECT DISTINCT title,year FROM MovieAwards WHERE EXISTS (SELECT DISTINCT * FROM Movies WHERE MovieAwards.title = Movies.title AND Movies.year = MovieAwards.year AND Movies.year >= 2000 AND Movies.year <= 2010 AND MovieAwards.result='won'));

CodePudding user response：

You can GROUP BY title and year and count the result

SELECT COUNT(*)::FLOAT
  FROM MovieAwards 
  WHERE EXISTS (SELECT DISTINCT * FROM Movies WHERE MovieAwards.title = Movies.title AND Movies.year = MovieAwards.year AND Movies.year >= 2000 AND Movies.year <= 2010 AND MovieAwards.result='won')
GROUP BY "title","year";

count

SELECT 0

fiddle