CODEWITHSUNDEEP
mysqlgroup-bystatus
aprilleocean
aprilleocean

Reputation: 121

How to use mysql group by query

I have a table with columns Name, EmailId, Process, Status like below.

**Input**

   Name       EmailId                Process      Status    
   apple1     [email protected]       process1     Closed
   apple2     [email protected]       process2     In Progress
   apple3     [email protected]       process3     Complete      
   orange1    [email protected]      process1     Closed
   orange2    [email protected]      process2     Closed
   oran       [email protected]      process3     Closed
   mango1     [email protected]       process1     To Start

Now what I would like to have is the emailId and Status of people whose Status are closed for all the Process - Process1, process2, process 3

**Output**

EmailId              Status    
[email protected]    Closed

Since all the process for orange have been closed I have the above row Similarly I would like to have all the email ids of the table whose process are closed.

Upvotes: 1

Views: 106

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Another way:

SELECT 
    EmailId
FROM
    tableX AS t
GROUP BY 
    EmailId
HAVING
    COUNT( CASE WHEN Status <> 'Closed'
             THEN 1 
           END
         ) = 0 ;

or:

SELECT 
    t.EmailId
FROM
    ( SELECT DISTINCT EmailId
      FROM tableX 
    ) AS t
  LEFT JOIN
    tableX AS tt
      ON  tt.EmailId = t.EmailId
      AND tt.Status <> 'Closed'
WHERE
    tt.EmailId IS NULL ;

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270617

To find this, you can use an aggregate COUNT() to find that the count = 3 in a HAVING clause, hence all three are closed:

SELECT
  EmailId,
  /* How many different processes are closed per EmailId */
  COUNT(DISTINCT Process) AS numclosed
FROM
  yourtable
WHERE Status = 'Closed'
GROUP BY EmailId
/* Return only those with 3 processes closed */
HAVING numclosed = 3

If the number of possible processes isn't exactly 3 and you need to account for all of them, I believe you can use a subquery in the HAVING clause to find the actual number of possible processes

SELECT
  EmailId,
  COUNT(DISTINCT Process) AS numclosed
FROM
  yourtable
WHERE Status = 'Closed'
GROUP BY EmailId
/* Compare the number closed for this EmailId against the number of possible Process values in the table */
HAVING numclosed = (SELECT COUNT(DISTINCT Process) FROM yourtable)

Upvotes: 3

Related Questions