SQL SUM with CASE and DISTINCT?

Question

Continuing my last question ...

Let me try to explain my scheme. I have three tables that we will call users (with identifiers and column names), parties (with column identifiers, partydate and user_id) and questions (with column identifiers, createate and user_id). My requirement is to show each user the number of parties over the past year and the questions created over the past year.

My query looks like this:

SELECT users.id, users.name,  
  SUM(CASE WHEN (parties.party> NOW() - interval '1 year') THEN 1 ELSE 0 END) 
    AS numparties, 
  SUM(CASE WHEN (questions.createdate> NOW() - interval '1 year') THEN 1 ELSE 0 END)
    AS numquestions
FROM users
  LEFT JOIN parties ON users.id=parties.user_id
  LEFT JOIN questions ON users.id=questions.user_id
GROUP BY users.id, users.name;

100%. . , ( ) , . , , parties.id questions.id , GROUP BY, - :

user.id | user.name | parties.id | questions.id  
-----------------------------------------------
0          John          15             2
0          John          15             7

, . .

COUNT(), DISTINCT, SUM , . - :

SUM(CASE WHEN (parties.party> NOW() - interval '1 year' AND parties.id IS DISTINCT) THEN 1 ELSE 0 END) 
AS numparties, 

, , . ?

Answer 1

( ), .

:

  SELECT users.id, users.name, 
         subquery1.result_of_calculation1, subquery2.result_of_calculation2
    FROM users
         LEFT JOIN (
            --calculation 1
         ) subquery1
         ON users.id = subquery1.user_id
         LEFT JOIN (
            --calculation 2
         ) subquery2
         ON users.id = subquery2.user_id
GROUP BY users.id, users.name;

Answer 2

, :

SELECT users.id, users.name, 
  COALESCE(tparties.ecount,0),
  COALESCE(tquestions.pcount,0)
FROM users
  FULL JOIN (
    SELECT user_id,COUNT(parties.id) AS ecount 
    FROM parties 
    JOIN users ON parties.user_id = users.id 
      AND parties.partydate > NOW() - interval '1 year' 
    GROUP BY user_id) 
    as tparties ON users.id=tparties.user_id
  FULL JOIN (
    SELECT user_id,COUNT(questions.id) AS pcount 
    FROM questions JOIN users ON questions.user_id = users.id 
      AND questions.createdate > NOW() - interval '1 year' 
    GROUP BY user_id) 
    as tquestions ON users.id=tquestions.user_id
;

, . , user_id, , , , -, . , , , . , , , . .