CODEWITHSUNDEEP
sqlgroup-bywindow-functionsquartile
Amethyst
Amethyst

Reputation: 28

SQL Window Function - How to handle since unable to Group By

This is my first time posting here. I have been quietly browsing the forum for months.

I am trying to have the category name, quartile, and count of titles per quartile show up. This is my SQL code:

SELECT name, standard_quartile, count
FROM 
     (SELECT c.name, ntile(4) over (order by f.rental_duration) as 
     standard_quartile, count(f.title) as count
     FROM category c
     JOIN film_category fc ON c.category_id=fc.category_id
     JOIN film f ON fc.film_id=f.film_id 
     WHERE c.name='Animation' OR c.name='Children' OR c.name='Classics' OR 
     <BR>c.name='Comedy' OR c.name='Family' OR c.name='Music'
     <BR>GROUP BY c.name, f.rental_duration
     <BR>) t1
GROUP BY 1, 2, 3
ORDER BY 1,2

However, because I am unable to group by the quartile (it's a window function), it is not displaying the results as I'd like. I thought putting it in a subquery might make it work, but it isn't. I believe the other issue is that one quartile can have more than one rental duration number associated with it.

This is what it looks like:

Animation   1 12
Animation   1 18
Animation   2 9
Animation   3 13
Animation   4 14
Children     1 12
Children     2 9
Children     2 15
Children     3 13
Children     4 11

If anyone can point me in the right direction or has helpful tips, I would greatly appreciate it.

The count and grouping is what I'm trying to fix. If you see in the snippet of the display results, there are two 1 quartiles for Animation and two 2 quartiles for Children. There should be one of each. But since I cannot group by quartile since it's a window function, it's grouping by rental_duration.

Thanks! :)

PS - This is what it is supposed to look like:

screenshot of expected query results

Upvotes: 1

Views: 143

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

I think you want the group by only in the outer query:

SELECT name, standard_quartile, count(*)
FROM (SELECT c.name,
             ntile(4) over (order by f.rental_duration) as standard_quartile
     FROM category c JOIN
          film_category fc
          ON c.category_id = fc.category_id JOIN
          film f
          ON fc.film_id = f.film_id 
     WHERE c.name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
    ) t1
GROUP BY name, standard_quartile
ORDER BY name, standard_quartile;

Also note the use of in for the list of categories.

Upvotes: 1

starko
starko

Reputation: 1149

select name , standard_quartile, sum(count)
    FROM(
    SELECT name, standard_quartile, count
    FROM 
         (SELECT c.name, ntile(4) over (order by f.rental_duration) as 
         standard_quartile, count(f.title) as count
         FROM category c
         JOIN film_category fc ON c.category_id=fc.category_id
         JOIN film f ON fc.film_id=f.film_id 
         WHERE c.name='Animation' OR c.name='Children' OR c.name='Classics' OR 
         <BR>c.name='Comedy' OR c.name='Family' OR c.name='Music'
         <BR>GROUP BY c.name, f.rental_duration
         <BR>) t1
    GROUP BY 1, 2, 3) t2
group by 1,2
ORDER BY 1,2

Upvotes: 0

Related Questions