Using multiple COUNT and SUM in a single SQL statement

Question

I have tables with the name of the page , page_views , page_items and page_votes . The last three tables contain the page_id foreign key to save a record of each individual view, item, and voice that belongs to the page.

When I request a page, I also want to get COUNT page_views , COUNT page_items and SUM page_votes.vote .

I inserted the request below. It retrieves the total number of views. I have made various attempts to add elements and vote for it, but the result is either a syntax error or representations / elements / voices returned as identical and “wrong” numbers, possibly due to the way I join.

How can I add elements and voices to this request?

SELECT
  Page.*,
  COUNT(*) AS views
FROM pages AS Page 
INNER JOIN page_views AS PageView
  ON Page.id = PageView.page_id 
GROUP BY Page.id 
ORDER BY views DESC   
LIMIT 10 OFFSET 0

Answer 1

This will allow you to select TOP 10 viewedpages and will only count elements and votes for these pages.

Effective if you have many pages, but they only need to 10, eliminates unnecessary counting.

SELECT  (
        SELECT COUNT(*)
        FROM   page_views
        WHERE  page_views.page_id = pages.id
        ) AS views_count,
        (
        SELECT COUNT(*)
        FROM   page_items
        WHERE  page_items.page_id = pages.id
        ) AS items_count,
        COALESCE(
        (
        SELECT SUM(vote)
        FROM   page_votes
        WHERE  page_votes.page_id = pages.id
        ), 0) AS votes_sum
FROM    pages
ORDER BY
        views_count DESC
LIMIT 10

Even more efficient query:

SELECT  pages.*,
        (
        SELECT COUNT(*)
        FROM   page_items
        WHERE  page_items.page_id = pages.id
        ) AS items_count,
        COALESCE(
        (
        SELECT SUM(vote)
        FROM   page_votes
        WHERE  page_votes.page_id = pages.id
        ), 0) AS votes_sum
FROM    (
        SELECT  page_id, COUNT(*) AS cnt
        FROM    page_views
        GROUP BY
                page_id
        ORDER BY cnt DESC
        LIMIT 10
        ) AS pvd,
        pages
WHERE  pages.id = pvd.page_id

eliminates unnecessary connections using pages.

Answer 2

, SQL-, 10 , :

SELECT p.*,
  (SELECT SUM(views) FROM page_views WHERE page_id = p.page_id) views,
  (SELECT SUM(votes) FROM page_votes WHERE page_id = p.page_id) votes,
  (SELECT SUM(items) FROM page_items WHERE page_id = p.page_id) items
FROM pages p
ORDER BY views DESC
LIMIT 10