Using multiple COUNTs and SUMs in a single SQL statement

Question

I have a tables called pages, page_views, page_items and page_votes. The latter three tables contain a page_id foreign key in order to keep a record of each individual view, item and vote that belongs to a page.

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

I have pasted a query below. It retrieves the total number of views. I've made various attempts to add items and votes to it, but the result is either a syntax error or views/items/votes returned as an identical and "wrong" number, probably due to the way I am joining.

How can I add items and votes to this query?

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

Quassnoi · Accepted Answer · 2009-03-18T12:56:40.230

This will select TOP 10 viewed pages, and will count items and votes only for these pages.

Efficient if you have lots of pages but need only 10 of them, eliminates unneeded 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 unneeded joins with pages.

Thanks! The query you gave before editing worked. The optimized version gives me an error #1054 - Unknown column 'pages.id' in 'where clause'. — ryonlife, Mar 06 '09 at 21:56

score 1 · Answer 2 · answered Mar 06 '09 at 21:58

Assuming I read your SQL correctly and you want the top 10 pages by views, try this:

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

Using multiple COUNTs and SUMs in a single SQL statement

2 Answers2

Linked