CODEWITHSUNDEEP
mysql
siddaramesh
siddaramesh

Reputation: 241

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.

Upvotes: 1

Views: 345

Answers (1)

Arulkumar
Arulkumar

Reputation: 13237

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.

Upvotes: 1

Related Questions