Sort and Select by distinct field when using an aggregate function

Question

I want to select all clicks and views for certain objects. Clicks and views are tracked in separate tables, but I want 1 row returned for an object with the views and clicks combined. This works correctly with the following SQL:

SELECT * FROM (
    SELECT distinct(title)
    ,ROW_NUMBER() OVER (ORDER BY id ASC) AS RowNum
    ,SUM(vws) as vws,SUM(clicks) as clicks,id FROM(
    SELECT l.id,l.title,0 as vws, COUNT(lc.id) as clicks
    FROM locs l
    INNER JOIN locs_clicks lc on l.id=lc.locid
    GROUP BY l.title,l.id
    UNION
    SELECT l.id,l.title,COUNT(lv.id) as vws,0 as clicks
    FROM locs l
    INNER JOIN locs_views lv on l.id=lv.locid
    GROUP BY l.title,l.id
    )t
    GROUP BY title,id
) as info
WHERE RowNum > 0 AND RowNum <= 100

I get a result like:

title           RowNum  vws clicks  id
Mercedes Benz   12697   43  2       17231289

However, when I want to sort on views (vws) and change 1 line:

`,ROW_NUMBER() OVER (ORDER BY vws ASC) AS RowNum`

I get the error:

Column 't.vws' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

But then when I then change to GROUP BY title,id,vws ending up with this query:

SELECT * FROM (
    SELECT distinct(title)
    ,ROW_NUMBER() OVER (ORDER BY vws ASC) AS RowNum
    ,sum(vws) as vws,sum(clicks) as clicks,id FROM(
    SELECT l.id,l.title,0 as vws, COUNT(lc.id) as clicks
    FROM locs l
    INNER JOIN locs_clicks lc on l.id=lc.objectid
    GROUP BY l.title,l.id
    UNION
    SELECT l.id,l.title,COUNT(lv.id) as vws,0 as clicks
    FROM locs l
    INNER JOIN locs_views lv on l.id=lv.objectid
    GROUP BY l.title,l.id
    )t
    GROUP BY title,id,vws
) as info
WHERE RowNum > 0 AND RowNum <= 100

The rows are no longer rolled up into 1 row by distinct title:

title           RowNum  vws clicks  id
Mercedes Benz   699     0   2       17231289
Mercedes Benz   18102   43  0       17231289

How can I select unique rows by title and have clicks and vws still in the same result/row returned?

Answer 1

Just move the ROW_NUMBER() OVER (ORDER BY vws ASC) in the outer query.

SELECT *
FROM
(
    SELECT title,vws,clicks,id,  
         ROW_NUMBER() OVER (ORDER BY vws ASC) AS RowNum
    FROM (
    
       SELECT distinct(title) as title,    
            SUM(vws) as vws,
            SUM(clicks) as clicks,
            id 
        FROM(
            SELECT l.id,l.title,0 as vws, COUNT(lc.id) as clicks
            FROM locs l
            INNER JOIN locs_clicks lc on l.id=lc.locid
            GROUP BY l.title,l.id
            UNION
            SELECT l.id,l.title,COUNT(lv.id) as vws,0 as clicks
            FROM locs l
            INNER JOIN locs_views lv on l.id=lv.locid
            GROUP BY l.title,l.id
            )t
            GROUP BY title,id
    
      ) as info
)as info
WHERE RowNum > 0 AND RowNum <= 100

dbfiddle