Akshay Shah Akshay Shah - 4 months ago 7
SQL Question

To apply DENSE_RANK() on the output of an query

I have three tables named Groups, GroupMembers and DailyTable respectively. Below are my models for each one of them:

models.py

class Group(models.Model):

name = models.CharField(max_length=255, blank=False, null=False)
group_type = models.ForeignKey('GroupType',blank=True,default=False)

class Meta:
db_table = 'groups'

class GroupMembers(models.Model):
group = models.ForeignKey('Group')
user = models.ForeignKey('User')
status = models.CharField(max_length=20)

class Meta:
db_table = 'group_members'
unique_together = ['group', 'user']

class DailyTable(models.Model):
class Meta:
managed = True
db_table = 'dailytable'

user_id = models.IntegerField(db_index=True)
calories = models.FloatField()
date = models.DateField()


I wanted to calculate total calories burned by each group indiviaully. So I fired up the below query :

select groups.id,groups.name as group_display_name,
(select count(*) from group_members where group_id = groups.id and status = 'accepted' and groups.group_type_id = 1) as total_members,
(select sum(calories) from dailytable where user_id in (select user_id from group_members where group_id = groups.id and groups.group_type_id = 1) and dailytable.date='2016-02-02') as total_calories,
(select case when exists(select * from group_members where group_id = groups.id and user_id = 3 and status = 'accepted' and groups.group_type_id = 1) then cast(1 as bit) else cast(0 as bit) end) as is_member_of_group
from groups
where groups.group_type_id = 1
order by total_calories desc
nulls last;


Below is the output which i get
enter image description here
Now I wanted to RANK the Groups based upon the total_calories that I have burned, so I make the below changes in the query :

....
dense_rank() over(order by total_calories)
from groups
....


Error: column "total_calories" does not exist

I want to apply RANK to the output of the query. Is there any way to achieve this.

Answer

This is because you are trying to use derived column alias in dense_rank(), which is not available there. Wrap query in CTE and apply dense_rank() on it:

WITH baseQuery AS (
    SELECT
        groups.id,
        groups.name AS group_display_name,
        (
            SELECT
                count(*)
            FROM group_members
            WHERE
                group_id = groups.id AND status = 'accepted' AND groups.group_type_id = 1) AS total_members,
        (
            SELECT
                sum(calories)
            FROM dailytable
            WHERE
                user_id IN (
                    SELECT
                        user_id
                    FROM group_members
                    WHERE
                        group_id = groups.id 
                        AND groups.group_type_id = 1
                ) AND dailytable.date = '2016-02-02'
        ) AS total_calories,
        (
            SELECT
                CASE WHEN exists(SELECT
                                     *
                                 FROM group_members
                                 WHERE
                                     group_id = groups.id 
                                     AND user_id = 3 
                                     AND status = 'accepted' 
                                     AND groups.group_type_id = 1)
                    THEN cast(1 AS BIT)
                ELSE cast(0 AS BIT) END) AS is_member_of_group
    FROM groups
    WHERE
        groups.group_type_id = 1
)
    select * ,
        dense_rank() over(order by total_calories)
    from baseQuery
    ORDER BY total_calories DESC
    NULLS LAST
Comments