CODEWITHSUNDEEP
mysqlsql
Hitesh Shetty
Hitesh Shetty

Reputation: 21

Need exact output

I am new to SQL. I need to know SQL query to get the output of the following scenario.

I have a table Campaign with columns 

Customer ID, Campaign Name, Campaign ID, Channel

The Channel can have one of these values:

Direct Email, Email, Both

How do I evaluate the channel through which the customer have been contacted for the campaign? For example, there are a few customers who have been contacted through both channels.

How do I get a count of customers who have been contacted through both channels? Kindly help me with the query.

Upvotes: 0

Views: 67

Answers (1)

Agniveer
Agniveer

Reputation: 371

Its simple, I've given an example. You can check it manually.

select count(*) from (select count(*) as b from Campaign group by customer_id) as a where a.b=2;

Here, first we grouped them on the basis of customer_id which I've assumed to be distinct. Then counted those id's for which both channels have been used.

This is dummy case:

+------+------+
| id   | ch   |
+------+------+
|    1 | d    |
|    2 | d    |
|    2 | e    |
|    3 | d    |
|    4 | d    |
|    4 | e    |
|    5 | d    |
|    6 | e    |
+------+------+

inner query: (select count(*) from q group by id);
output: 

       +----------+
        | count(*) |
        +---------+
        |        1 |
        |        2 |
        |        1 |
        |        2 |
        |        1 |
        |        1 |
        +----------+
whole query: mysql> select count(*) 
from (select count(*) as b from q group by id) as a where a.b=2;
        +----------+
        | count(*) |
        +----------+
        |        2 |
        +----------+

1 row in set (0.00 sec)

Upvotes: 1

Related Questions