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