2
SELECT SUM(case when p.status = 2 then p.value end) as 'val_accepted'
      FROM
        props AS p
            INNER JOIN (p_contents AS pc
              INNER JOIN contents AS c ON c.id = pc.library_id)
            ON p.id = pc.prop_id
      WHERE p.account_id = 3
      GROUP BY (pc.library_id)

so, what's happening: there are two p_contents that are associated with a prop. those two p_contents have the same library_id which points to a corresponding content.
So, the SUM of p.value is double what it should be because there are two p_contents that point to the same content

How do I not double SUM the p.value?

EDIT:
I figured out how to use DISTINCT, but I still need access to the inner columns... 

SELECT    c.name as 'library_name',
   SUM(case when p.status = 2 then p.value end) as 'val_accepted',

FROM
  props AS p
  INNER JOIN 
  (
    SELECT DISTINCT(pc.library_id), prop_id
    FROM prop_contents AS pc
    INNER JOIN 
    (
      SELECT name, visibility, id, updated_at 
      FROM contents AS c
    ) as c
      ON c.id = pc.library_id
  )as pc
  ON p.id = pc.prop_id
 WHERE p.account_id = 3
 GROUP BY (pc.library_id)

and now I get the error:

Unknown column 'c.name' in 'field list')
NullVoxPopuli
  • 61,906
  • 73
  • 206
  • 352

1 Answers1

1

Here's one solution. First reduce the set to distinct rows in an derived table, then apply the GROUP BY to that result:

SELECT SUM(case when d.status = 2 then d.value end) as 'val_accepted'
FROM (
  SELECT DISTINCT p.id, p.status, p.value, pc.library_id
  FROM props p
  INNER JOIN p_contents AS pc ON p.id = pc.prop_id
  INNER JOIN contents AS c ON c.id = pc.library_id
  WHERE p.account_id = 3) AS d
GROUP BY d.library_id

You use DISTINCT(pc.library_id) in your example, as if DISTINCT applies only to the column inside the parentheses. This is a common misconception. DISTINCT applies to all columns of the select-list. DISTINCT is not a function; it's a query modifier.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828