nikhilboorla Posted March 3, 2020 Report Posted March 3, 2020 I want to find the no of invitations sent,failed and delivered for each email domain like Google,Yahoo,MSN,Outlook,Hotmail,AOL. I have used case function to count no of invitation sent from to_email column. Invitations sent column name is sent,Invitations failed column name is bounced and Invitations Delivered column name is Bounced select SUM(case when r.to_email like '%gmail%' then 1 else 0 end) as Google, SUM(case when r.to_email like '%yahoo%' then 1 else 0 end) as Yahoo, SUM(case when r.to_email like '%msn%' then 1 else 0 end) as MSN, from r4e_mongo.mongo_repbiz_request_reviews r left join r4e_mongo.mongo_repbiz_comments c on r.id = c.id and r.location_id = c.location_id but the required output should be like --------------------- |----------|----------|-----------|----------- | Google | Yahoo | Outlook | MSN ----------------------|----------|----------|-----------|------------ Invitations sent | | | | Invitations failed | | | | Invitations Delivered | | | | Quote
Thadimattaya Posted March 3, 2020 Report Posted March 3, 2020 1 hour ago, nikhilboorla said: I want to find the no of invitations sent,failed and delivered for each email domain like Google,Yahoo,MSN,Outlook,Hotmail,AOL. I have used case function to count no of invitation sent from to_email column. Invitations sent column name is sent,Invitations failed column name is bounced and Invitations Delivered column name is Bounced select SUM(case when r.to_email like '%gmail%' then 1 else 0 end) as Google, SUM(case when r.to_email like '%yahoo%' then 1 else 0 end) as Yahoo, SUM(case when r.to_email like '%msn%' then 1 else 0 end) as MSN, from r4e_mongo.mongo_repbiz_request_reviews r left join r4e_mongo.mongo_repbiz_comments c on r.id = c.id and r.location_id = c.location_id but the required output should be like --------------------- |----------|----------|-----------|----------- | Google | Yahoo | Outlook | MSN ----------------------|----------|----------|-----------|------------ Invitations sent | | | | Invitations failed | | | | Invitations Delivered | | | | google chesi Pivot and Unpivot expamples chudu.. Neku kavalsina output lo osthadhi Quote
nikhilboorla Posted March 3, 2020 Author Report Posted March 3, 2020 57 minutes ago, Thadimattaya said: google chesi Pivot and Unpivot expamples chudu.. Neku kavalsina output lo osthadhi output vachindi but how to create first rows like Invitations sent and Invitations failed ..... i am getting output like --------------------- |----------|----------|-----------|----------- | Google | Yahoo | Outlook | MSN ----------------------|----------|----------|-----------|------------ | 863 | 769 | 128 | 79 Quote
Srin Posted March 3, 2020 Report Posted March 3, 2020 4 hours ago, nikhilboorla said: I want to find the no of invitations sent,failed and delivered for each email domain like Google,Yahoo,MSN,Outlook,Hotmail,AOL. I have used case function to count no of invitation sent from to_email column. Invitations sent column name is sent,Invitations failed column name is bounced and Invitations Delivered column name is Bounced select SUM(case when r.to_email like '%gmail%' then 1 else 0 end) as Google, SUM(case when r.to_email like '%yahoo%' then 1 else 0 end) as Yahoo, SUM(case when r.to_email like '%msn%' then 1 else 0 end) as MSN, from r4e_mongo.mongo_repbiz_request_reviews r left join r4e_mongo.mongo_repbiz_comments c on r.id = c.id and r.location_id = c.location_id but the required output should be like --------------------- |----------|----------|-----------|----------- | Google | Yahoo | Outlook | MSN ----------------------|----------|----------|-----------|------------ Invitations sent | | | | Invitations failed | | | | Invitations Delivered | | | | Use pivot in sql Quote
Chinna84 Posted March 3, 2020 Report Posted March 3, 2020 1 hour ago, nikhilboorla said: output vachindi but how to create first rows like Invitations sent and Invitations failed ..... i am getting output like --------------------- |----------|----------|-----------|----------- | Google | Yahoo | Outlook | MSN ----------------------|----------|----------|-----------|------------ | 863 | 769 | 128 | 79 Post query u used Quote
anjilenajole Posted March 3, 2020 Report Posted March 3, 2020 can you post the details of the tables as there are no more column details available from your query with which we cannot know about the other details. post at least 1 row level details from each table so that we can give you the exact query that you are looking for. Quote
Thadimattaya Posted March 3, 2020 Report Posted March 3, 2020 3 hours ago, nikhilboorla said: output vachindi but how to create first rows like Invitations sent and Invitations failed ..... i am getting output like --------------------- |----------|----------|-----------|----------- | Google | Yahoo | Outlook | MSN ----------------------|----------|----------|-----------|------------ | 863 | 769 | 128 | 79 1st unpivot chesi ochina result medha pivot use cheyali. Below example tho try cheyandi SELECT CustSiteId ,CustomerNumber ,SiteNumber ,B,D,E,F,G,H,W,[Empty] Into #SchedFreq_NS FROM ( SELECT CustSiteId ,CustomerNumber ,SiteNumber ,CASE WHEN Value = '' THEN 'Empty' ELSE Value END AS Value ,Count(CASE WHEN Value = '' THEN 'Empty' ELSE Value END) AS Count_Value FROM ( SELECT CustSiteId ,CustomerNumber ,SiteNumber ,SchedFreq FROM #NewStrategy ) Unp Unpivot(Value FOR Name IN (SchedFreq)) AS Val GROUP BY CustSiteId ,CustomerNumber ,SiteNumber ,Value ) A Pivot(Min(Count_Value) FOR Value IN (B,D,E,F,G,H,W,[Empty])) AS P Quote
afdb002 Posted March 3, 2020 Report Posted March 3, 2020 7 hours ago, nikhilboorla said: I want to find the no of invitations sent,failed and delivered for each email domain like Google,Yahoo,MSN,Outlook,Hotmail,AOL. I have used case function to count no of invitation sent from to_email column. Invitations sent column name is sent,Invitations failed column name is bounced and Invitations Delivered column name is Bounced select SUM(case when r.to_email like '%gmail%' then 1 else 0 end) as Google, SUM(case when r.to_email like '%yahoo%' then 1 else 0 end) as Yahoo, SUM(case when r.to_email like '%msn%' then 1 else 0 end) as MSN, from r4e_mongo.mongo_repbiz_request_reviews r left join r4e_mongo.mongo_repbiz_comments c on r.id = c.id and r.location_id = c.location_id but the required output should be like --------------------- |----------|----------|-----------|----------- | Google | Yahoo | Outlook | MSN ----------------------|----------|----------|-----------|------------ Invitations sent | | | | Invitations failed | | | | Invitations Delivered | | | | Invitations sent , Invitations failed & Invitations Delivered identify cheyyaniki column ekkadundi.. danni vadu Quote
nikhilboorla Posted March 4, 2020 Author Report Posted March 4, 2020 22 hours ago, Chinna84 said: Post query u used select SUM(case when r.to_email like '%gmail%' then 1 else 0 end) as Google, SUM(case when r.to_email like '%yahoo%' then 1 else 0 end) as Yahoo, SUM(case when r.to_email like '%msn%' then 1 else 0 end) as MSN, SUM(case when r.to_email like '%outlook%' then 1 else 0 end) as Outlook, SUM(case when r.to_email like '%hotmail%' then 1 else 0 end) as Hotmail, SUM(case when r.to_email like '%aol%' then 1 else 0 end) as AOL, SUM(case when r.to_email like '%other%' then 1 else 0 end) as Other from r4e_mongo.mongo_repbiz_request_reviews r left join r4e_mongo.mongo_repbiz_comments c on r.id = c.id and r.location_id = c.location_id and date(c.date) > '2019-04-01' Quote
Chinna84 Posted March 4, 2020 Report Posted March 4, 2020 1 hour ago, nikhilboorla said: select SUM(case when r.to_email like '%gmail%' then 1 else 0 end) as Google, SUM(case when r.to_email like '%yahoo%' then 1 else 0 end) as Yahoo, SUM(case when r.to_email like '%msn%' then 1 else 0 end) as MSN, SUM(case when r.to_email like '%outlook%' then 1 else 0 end) as Outlook, SUM(case when r.to_email like '%hotmail%' then 1 else 0 end) as Hotmail, SUM(case when r.to_email like '%aol%' then 1 else 0 end) as AOL, SUM(case when r.to_email like '%other%' then 1 else 0 end) as Other from r4e_mongo.mongo_repbiz_request_reviews r left join r4e_mongo.mongo_repbiz_comments c on r.id = c.id and r.location_id = c.location_id and date(c.date) > '2019-04-01' where are the columns for Invitation_Sent,Invitation_Delivered,Invitation_Bounced in the above statement ?? You should have some other columns you need to consider, so that we can pivot the "I_S,I_D, & I_B" columns to rows. or let me put in this way, where do you get data for "Invitation_Sent" ? I tried this on my side using our tables which is working fine - it's a basic query I used, if you figure out how we can data for I_S,I_D & I_B columns, we can do the remaining part. Select c.SentColumn_Name as Invitations_Sent, (--just assuming u r getting data from this table) c.BouncedColumn_Name as Invitations_Bounced, SUM(case when r.email like '%gmail%' then 1 else 0 end) as Google, SUM(case when r.email like '%yahoo%' then 1 else 0 end) as Yahoo, SUM(case when r.email like '%msn%' then 1 else 0 end) as MSN From r4e_mongo.mongo_repbiz_request_reviews r left join r4e_mongo.mongo_repbiz_comments c on r.id = c.id and r.location_id = c.location_id and date(c.date) > '2019-04-01' Quote
nikhilboorla Posted March 5, 2020 Author Report Posted March 5, 2020 21 hours ago, Chinna84 said: where are the columns for Invitation_Sent,Invitation_Delivered,Invitation_Bounced in the above statement ?? You should have some other columns you need to consider, so that we can pivot the "I_S,I_D, & I_B" columns to rows. or let me put in this way, where do you get data for "Invitation_Sent" ? I tried this on my side using our tables which is working fine - it's a basic query I used, if you figure out how we can data for I_S,I_D & I_B columns, we can do the remaining part. Select c.SentColumn_Name as Invitations_Sent, (--just assuming u r getting data from this table) c.BouncedColumn_Name as Invitations_Bounced, SUM(case when r.email like '%gmail%' then 1 else 0 end) as Google, SUM(case when r.email like '%yahoo%' then 1 else 0 end) as Yahoo, SUM(case when r.email like '%msn%' then 1 else 0 end) as MSN From r4e_mongo.mongo_repbiz_request_reviews r left join r4e_mongo.mongo_repbiz_comments c on r.id = c.id and r.location_id = c.location_id and date(c.date) > '2019-04-01' ila chesthe output ila vasthindi ---------------------|-------------------------|-----------------------------|---------- |----------|-----------|----------- Invitations_Sent | Invitations_Bounced | Invitations Delivered | Google | Yahoo | Outlook | MSN ---------------------|-------------------------|-----------------------------|---------- |----------|-----------|------------ 64983 74839 27469 487 274 633 57 Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.