postgresql subquery count within a larger context

Question

I have the following query:

 SELECT sum(p.price) as "totalPurchase", sum(s.price) as "totalSale", sum(er.price) as          
 "totalRet", v.name as "vendor"
 FROM "Esns" e
 JOIN "EsnsPurchaseOrderItems" ep on ep."EsnId" = e.id
 JOIN "PurchaseOrderItems" p on ep."PurchaseOrderItemId"= p.id
 JOIN "PurchaseOrders" po on p."PurchaseOrderId" = po.id
 JOIN "Vendors" v on po."VendorId" = v.id
 LEFT OUTER JOIN "EsnsSalesOrderItems" es on es."EsnId" = e.id
 LEFT OUTER JOIN "SalesOrderItems" s on es."SalesOrderItemId" = s.id
 LEFT OUTER JOIN "EsnsRmas" er on er."EsnId" = e.id
 GROUP BY v.id

However, i want to also count all e.id that are organized by the above join along with the field e."inStock" = true.

if i simply do this (select count(e.*) where e."inStock" ) as "inStock"-- it gives me the correct info but for some reason splits up the results incorrectly, meaning where there was one line items with 50,000 in sales there are now two with 25,000 — user1175817
– user1175817, Commented Feb 26, 2013 at 16:25

John Woo · Accepted Answer · 2013-02-26 16:22:12Z

1

SELECT  sum(p.price) as "totalPurchase", 
        sum(s.price) as "totalSale", 
        sum(er.price) as "totalRet", 
        v.name as "vendor",
        sum(CASE WHEN e."inStock" = true THEN 1 ELSE 0 END) as "totalInStock"
FROM    .....
GROUP   BY ....

OR

COUNT(CASE WHEN e."inStock" = true THEN 1 END) as "totalInStock"

answered Feb 26, 2013 at 16:22

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

2 Comments

user1175817 Over a year ago

this has the same problem i mention in my above comment. Incorrectly splitting up the rows

user1175817 Over a year ago

sorry i had forgotten to include that bit of info in my original comment

Collectives™ on Stack Overflow

postgresql subquery count within a larger context

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related