Sample Header Ad - 728x90

Postgresql group by for many columns

1 vote
1 answer
276 views
I have such query:
SELECT "Movie"."title", 
       "Movie"."id", 
	   "Movie"."publicationDate", 
	   "Movie"."authorID", 
	   "Author"."photoURL" as "authorPhoto", 
	   concat("Author"."name", ' ', "Author"."surname") as "authorName",
	   "Movie"."seriesID", 
	   "Series"."name" as "seriesName", 
	   "Series"."logoURL" as "seriesLogo", 
	   "Movie"."languageID" as "language", 
	   "Movie"."ratingUp", 
	   "Movie"."ratingDown", 
	   "userWatched"."watchedDate", 
	   CASE WHEN "UserMovieFavourited"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' 
	        THEN TRUE 
			ELSE FALSE 
			END as "favourited", 
	   CASE WHEN "UserMovieWatchlist"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' 
	        THEN TRUE 
			ELSE FALSE 
			END as "onWatchlist",
       CASE WHEN "userWatched"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' AND "userWatched"."watchedDate" IS NOT NULL 
	        THEN TRUE 
			ELSE FALSE 
			END as "watched",
			count(DISTINCT "overallWatched"."watchedDate") as "viewCount" 
FROM "Movie" 
LEFT JOIN "Series" ON "Series"."id" = "Movie"."seriesID" 
LEFT JOIN "Author" ON "Author"."id" = "Movie"."authorID" 
LEFT JOIN "UserMovieFavourited" ON "UserMovieFavourited"."movieID" = "Movie"."id" 
								AND "UserMovieFavourited"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' 
LEFT JOIN "UserMovieWatchlist" ON "UserMovieWatchlist"."movieID" = "Movie"."id" 
							   AND "UserMovieWatchlist"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' 
LEFT JOIN "UserMovieWatchProgress" as "userWatched" ON "userWatched"."movieID" = "Movie"."id" 
													AND "userWatched"."userID" = '6F9BD058-853F-4B50-92D6-9170118A2727' 
LEFT JOIN "UserMovieWatchProgress" as "overallWatched" ON "overallWatched"."movieID" = "Movie"."id" 
GROUP BY "Movie"."id", 
		 "Author"."photoURL", 
		 "Author"."name", 
		 "Author"."surname", 
		 "Series"."name", 
		 "Series"."logoURL", 
		 "userWatched"."watchedDate", 
		 "UserMovieFavourited"."userID", 
		 "UserMovieWatchlist"."userID", 
		 "userWatched"."userID"
In MySQL i needed only one column for grouping that's Movie.id. For postgresql I needed to add 10 columns to make this query work as shown above. Is this natural for postgresql or this query should be refactored to remove need of so many grouping columns?
Asked by Paweł Madej (123 rep)
Feb 11, 2020, 06:08 PM
Last activity: May 23, 2025, 11:04 AM