EMAIL        LAUNCHID     ACTION
a@fgy.com    43           Sent
a@fgy.com    43           Opened
a@fgy.com    43           Clicked
a@fgy.com    44           Sent
a@fgy.com    44           Bounced
bdf@df.com   35           Skipped
bdf@df.com   37           Skipped
uwyb@yrt.com 43           Sent   
uwyb@yrt.com 44           Sent

/*
Create Table #test (email varchar(50), lid int, action varchar(50))
Insert Into #test Select 'a@fgy.com', 43, 'Sent'
Insert Into #test Select 'a@fgy.com', 43, 'Opened'
Insert Into #test Select 'a@fgy.com', 43, 'Clicked'
Insert Into #test Select 'a@fgy.com', 44, 'Sent'
Insert Into #test Select 'a@fgy.com', 44, 'Bounced'
Insert Into #test Select 'bdf@df.com', 35, 'Skipped'
Insert Into #test Select 'bdf@df.com', 37, 'Skipped'
Insert Into #test Select 'uwyb@yrt.com', 43, 'Sent'
Insert Into #test Select 'uwyb@yrt.com', 44, 'Sent'
*/

Select T.email,
Sum(
	Case 
		When Sent.lid Is Not Null 
		And Bounced.lid Is Null 
		Then 1 
		Else 0 
	End
) As LaunceshSent,
Sum(
	Case 
		When Sent.lid Is Not Null 
		And Bounced.lid Is Null 
		And Opened.lid Is Not Null
		Then 1 
		Else 0 
	End
) As LaunchesOpened

From (Select Distinct email, lid From #test) T
Left Outer Join
(
	Select Distinct email, lid From #test 
	Where action In ('Sent')
) Sent On T.email = Sent.email and T.lid = Sent.lid
Left Outer Join
(
	Select Distinct email, lid From #test 
	Where action In ('Bounced')
) Bounced On T.email = Bounced.email and T.lid = Bounced.lid
Left Outer Join
(
	Select Distinct email, lid From #test 
	Where action In ('Opened', 'Clicked')
) Opened On T.email = Opened.email and T.lid = Opened.lid

Group By T.email