Darshan Patel Darshan Patel - 1 month ago 5
SQL Question

Postgres : get min and max rows count in many to many relation table

I have mapping table for RFQ(request for quotation) and Vendor's bid amount.

rfq_vendor_mapping :

id rfq_id(FK) vendor_id(FK) amount
---------------------------------------

1 1 1 100
2 1 2 50
3 2 1 200
4 2 3 300
5 2 2 40
6 3 4 70
7 3 1 90
8 3 2 250
9 4 3 30
10 5 1 500


In above table, I want analysis for how many times vendor has submitted minimum and maximum bid for each RFQ.

Expected Output :

vendor_id min_bid_count max_bid_count
-----------------------------------------
1 1 2
2 2 1
3 1 2
4 1 0


Note : It would be really great if query is supported in JPA.

http://sqlfiddle.com/#!15/60198/1

Answer

Compare the vendor's amount with min and max from a window function and run a conditional count on outer query level:

SELECT vendor_id
     , count(min_bid OR NULL) AS min_bid_count
     , count(max_bid OR NULL) AS max_bid_count
FROM  (
   SELECT vendor_id
        , amount = min(amount) OVER w AS min_bid
        , amount = max(amount) OVER w AS max_bid
   FROM   rfq_vendor_mapping
   WINDOW w AS (PARTITION BY rfq_id)
   ) sub
GROUP  BY 1
ORDER  BY 1;

SQL Fiddle.

Comments