How to Sumif a Sum

Ryan Moran picture Ryan Moran · Feb 7, 2017 · Viewed 9k times · Source

I am trying to get a "total override" but only sum if the agent = x,y or z

SELECT   
    DISTINCT( "public"."rdf_dean"."agent_name" )  AS "Agent",
    SUM("public"."rdf_dean"."paidcomm" *.9) AS "Paid to Agent",
    SUM("public"."rdf_dean"."paidcomm" *.1) AS "Overrides",
    SUM overrides IF agent_name = x OR agent_name = y OR agent_name = z

FROM     "public"."rdf_dean"
WHERE "public"."rdf_dean"."created_date" = date(now())
GROUP BY agent_name

Answer

Roman Tkachuk picture Roman Tkachuk · Feb 7, 2017

If you want to have both aggregates for all rows and for some you can use FILTER (https://www.postgresql.org/docs/9.4/static/sql-expressions.html):

SELECT   
    "public"."rdf_dean"."agent_name" AS "Agent",
    SUM("public"."rdf_dean"."paidcomm" *.9) AS "Paid to Agent",
    SUM("public"."rdf_dean"."paidcomm" *.1) AS "Overrides",
    SUM("public"."rdf_dean"."paidcomm" *.1) 
        FILTER (WHERE agent_name = x OR agent_name = y OR agent_name = z)
        AS "Partial Overrides",
FROM     "public"."rdf_dean"
WHERE "public"."rdf_dean"."created_date" = date(now())
GROUP BY agent_name