0

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?

1 Answer 1

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.