up vote 0 down vote favorite

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 ;
share|improve this question
83% accept rate
1  
It appears that there is no relation between the query for shippedUnits and the outer query - ie, it will produce the same value in all rows. Is that what it is supposed to do? Or is the subquery supposed to vary per row? – jcern 15 hours ago
its supposed to vary per row. ShippedUnits is just EsnsVmas that are approved and shipped. Units is just EsvVmas that are approved and not shipped. Your point about relationship is correct actually. But that is only because i modified the question and took out a bunch of other joins and fields that i thought werent necessary. – user1175817 14 hours ago
Do i need to add them back in? or you can answer the question of how to do two counts with those fields using joins in this fashion – user1175817 14 hours ago
it might help to see the original query. Also, since you have the group by clause - doesn't having the VMAS.* in the select give you a "must appear in the GROUP BY clause" error? – jcern 14 hours ago
no thats fine. But please check the full query – user1175817 14 hours ago
feedback

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
yes thats great thank you very much. One last thing. How do i get shippedUnits to =0 if the count is 0 – user1175817 14 hours ago
its hard coded to 2 here but in actuality that number is dynamically generated – user1175817 14 hours ago
The count function should return 0 if no records are returned. But, if it is not you could try to either to 0 + (select ...) to force it to a number, or use a case statement to match what you are receiving and return 0 in that case. postgresql.org/docs/9.1/static/functions-conditional.html One of those two should work. – jcern 14 hours ago
ok thank you again – user1175817 14 hours ago
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.