CODEWITHSUNDEEP
postgresqlsubquery
user1175817
user1175817

Reputation: 489

postgresql subquery count within a larger context

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.

Upvotes: 0

Views: 682

Answers (1)

John Woo
John Woo

Reputation: 263723

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"

Upvotes: 1

Related Questions