siddaramesh siddaramesh - 4 months ago 7
SQL Question

How to display partial pending in status column when column contains both pending and completed in mysql

I want to display partial pending in my status column when status column contains both pending and completed.

Table:

Enquiry Name Part Number Status
------------------------------------------
Enq1 aar-12332 Pending
Enq1 aar-12555 Completed
Enq2 aar-12666 Pending


Expected Result:

Enquiry Name Status
----------------------------------
Enq1 Partial Pending
Enq2 Pending


I wrote and tried many query but I did not get as expected.

Please Help me with this.

Answer

By using the approach below, you can achieve your expected result:

-- Get 'Partial Pending'
SELECT EnquiryName, 'Partial Pending' AS `Status`
FROM EnquiryTable 
WHERE `Status` IN ('Pending', 'Completed' )  
GROUP BY EnquiryName
HAVING COUNT(DISTINCT `Status`) = 2

UNION

-- Get the remaining Status
SELECT EnquiryName, `Status`
FROM EnquiryTable 
WHERE `Status` IN ('Pending', 'Completed' )  
GROUP BY EnquiryName
HAVING COUNT(DISTINCT `Status`) <> 2

Result:

EnquiryName     Status
Enq1            Partial Pending
Enq2            Pending

Reference from this post

SQL Fiddle DEMO for the same.