Sql query

[nikhilboorla]

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 |          |          |           |

 

[Thadimattaya]

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

[nikhilboorla]

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   

 

[Srin]

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

[Chinna84]

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

[anjilenajole]

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.

 

[Thadimattaya]

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
 

[afdb002]

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

[nikhilboorla]

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' 

[Chinna84]

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' 

[nikhilboorla]

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

[kittaya]

vuu