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?