postgresql double counts from join

Question

so i have the following query

SELECT SUM(s.price) as "totalSales", 
       sum(CASE 
             WHEN er.verified AND er.price = 1.010101 THEN s.price 
             WHEN er.verified AND er.price > 1.010101 THEN er.price ELSE 0 END) as "totalRet", 
       c.company as "client"
FROM "Esns" e 
  JOIN "EsnsSalesOrderItems" es on es."EsnId" = e."id" 
  JOIN "SalesOrderItems" s on es."SalesOrderItemId" = s.id
  JOIN "SalesOrders" so on so.id = s."SalesOrderId"
  JOIN "Clients" c on so."ClientId" = c.id                    
  LEFT OUTER JOIN  "EsnsRmas"  er on er."EsnId" = e.id
GROUP BY c.id

However, if there are multiple "EsnId" on es, it will count er multiple times and vice versa.

How do i structure so that everything is only counted one time?

Clodoaldo Neto · Accepted Answer · 2013-02-27 17:40:13Z

1

SELECT
    SUM(s.price) as "totalSales", 
    sum(CASE 
        WHEN er.verified AND er.price = 1.010101 THEN s.price 
        WHEN er.verified AND er.price > 1.010101 THEN er.price ELSE 0 END) as "totalRet", 
    c.company as "client"
FROM "Esns" e 
    JOIN (
        select distinct "EsnId"
        from "EsnsSalesOrderItems"
    ) es on es."EsnId" = e."id" 
    JOIN "SalesOrderItems" s on es."SalesOrderItemId" = s.id
    JOIN "SalesOrders" so on so.id = s."SalesOrderId"
    JOIN "Clients" c on so."ClientId" = c.id                    
    LEFT OUTER JOIN  "EsnsRmas"  er on er."EsnId" = e.id
GROUP BY c.id

answered Feb 27, 2013 at 17:40

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1175817 Over a year ago

so that doesnt work because it will only count one es if there are multiples. What i need to happen is for it to count the multiple es but then to know that it shouldnt be counting er again because it already counted it previously.

Collectives™ on Stack Overflow

postgresql double counts from join

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related