CODEWITHSUNDEEP
sqlpostgresql
Collin Smith
Collin Smith

Reputation: 17

Joining 2 tables and a view to get desired result in PostgreSQL

This is a view created using the query: SELECT title, count(*) as views FROM articles a, log l WHERE a.slug=substring(l.path, 10) GROUP BY title ORDER BY views DESC;

      title   | views
 ------------------------------------+--------
  article-1   | 338647
  article-2   | 253801
  article-5   | 170098
  article-3   |  84906
  article-8   |  84810
  article-7   |  84557
  article-6   |  84504
  article-4   |  84383

Now I'm trying to use this view to create a query to print out the sum of article views for each author associated with the titles. Below I have recreated the key columns of the other two tables. 

  AUTHORS
           name       | id
 ---------------------+--------
          author-1    | 1
          author-2    | 2
          author-3    | 3
          author-4    | 4

 ARTICLES
    author     | article
 --------------+--------
          1    | article-1
          2    | article-2
          1    | article-3
          4    | article-4
          1    | article-5
          1    | article-6
          3    | article-7
          2    | article-8

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You would do:

select a.author, sum(v.views) as view
from yourview v join
     articles a
     on v.title = a.article join
     authors au
     on a.author = au.id
group by a.author;

Upvotes: 0

Related Questions