CODEWITHSUNDEEP
postgresqljoin
user1175817
user1175817

Reputation: 489

postgres sql query counting two sets of objects

I have this join query below. I am having one issue with it. The shippedUnits column comes out wrong. It just counts the total items. The issue is I am counting two sets of objects that have everything in common except the shipped field. 

SELECT count(*) as "units", (select count("EsnsVmas".*) from "EsnsVmas" where
      "EsnsVmas"."shipped" = false AND "EsnsVmas"."VmaId" = 2 AND "EsnsVmas"."approved" = 
       true) as "shippedUnits", "Vmas".*, "Vendors"."name" as "name", "EsnsVmas".id as    
       "OrderItemId", "PurchaseOrderItems"."price", "Grades"."name" as "vendorGrade", 
       "Items"."name" as "model", "Items".id as "ItemId", "Grades".id as "GradeId" 
FROM "Vmas" 
      JOIN "EsnsVmas" on ("EsnsVmas"."VmaId" = "Vmas".id) 
      JOIN "Esns" on ("EsnsVmas"."EsnId" = "Esns".id) 
      JOIN "PurchaseOrderItems"  on ("Esns"."PurchaseOrderItemId"  =  
            "PurchaseOrderItems".id) 
      JOIN "Items" on ("PurchaseOrderItems"."ItemId" = "Items".id) 
      JOIN "Grades" on ("PurchaseOrderItems"."GradeId" = "Grades".id) 
      JOIN "Vendors" on ("Vmas"."VendorId"  = "Vendors".id)
 WHERE "Vmas".id =2 AND "EsnsVmas"."approved" = true 
 GROUP BY "Vmas".id, "PurchaseOrderItems".id, "Grades".id, "Items".id, "Vendors".id, "EsnsVmas".id ;

Upvotes: 0

Views: 166

Answers (1)

jcern
jcern

Reputation: 7848

It still seems that there is no relation between the inner and outer queries other than the hardcoded VmaId = "2". Since I don't have the tables in a database I can't test to make sure it works - but here's my first shot at a possible fix:

SELECT count(*) as "units", (select count("EsnsVmas".*) from "EsnsVmas" e2 where
      e2."shipped" = false AND e2."VmaId" = "Vmas".id AND e2."approved" = 
       true AND "EsnsVmas".id = e2.id) as "shippedUnits", "Vmas".*, "Vendors"."name" as "name", "EsnsVmas".id as    
       "OrderItemId", "PurchaseOrderItems"."price", "Grades"."name" as "vendorGrade", 
       "Items"."name" as "model", "Items".id as "ItemId", "Grades".id as "GradeId" 
FROM "Vmas" 
      JOIN "EsnsVmas" on ("EsnsVmas"."VmaId" = "Vmas".id) 
      JOIN "Esns" on ("EsnsVmas"."EsnId" = "Esns".id) 
      JOIN "PurchaseOrderItems"  on ("Esns"."PurchaseOrderItemId"  =  
            "PurchaseOrderItems".id) 
      JOIN "Items" on ("PurchaseOrderItems"."ItemId" = "Items".id) 
      JOIN "Grades" on ("PurchaseOrderItems"."GradeId" = "Grades".id) 
      JOIN "Vendors" on ("Vmas"."VendorId"  = "Vendors".id)
 WHERE "Vmas".id =2 AND "EsnsVmas"."approved" = true 
 GROUP BY "Vmas".id, "PurchaseOrderItems".id, "Grades".id, "Items".id, "Vendors".id, "EsnsVmas".id ;

This changed two things. First was relating back with "EsnsVmas".id = e2.id at the end of the subselect (I aliased the EsnsVmas table in the subselect to e2). The other thing was changing e2."VmaId" = "Vmas".id (instead of hardcoding it to 2) which shouldn't affect the output but allows you to change the id in only one place.

Upvotes: 1

Related Questions