CODEWITHSUNDEEP
sqlsql-serverselect
Antonio
Antonio

Reputation: 602

SELECT GROUP BY one criteria, COUNT another criteria

I did a marketing campaign, and some of my customer received 2 types of communication (A and B), and these types are split in 3 categories.

I have 2 tables in the SQL Server 2012 database: payment and contacts.

In the end what I need is a payment rate by SEGMENT (WHO PAID / WHO WAS CONTACT) I did that with no problem, but now the marketing team whats the rate group by a different way:

GROUP BY SEGMENT A, HOW MANY RECEIVED SEGMENT B AND HOW MANY PAID SEGMENT B

Like that:

+---------+----------+------+
| SEGMENT | RECEIVED | PAID |
+---------+----------+------+
| A1      | 2        | 1    | 
| A2      | 1        | 1    |
+---------+----------+------+

The content of the tables are:

CONTACTS

+----+---------+------------+
| ID | SEGMENT | DATE       |
+----+---------+------------+
| 1  | A1      | 2018-03-01 |
| 1  | B7      | 2018-03-10 |
| 2  | A2      | 2018-03-01 |
| 2  | B5      | 2018-03-10 |
| 3  | A1      | 2018-03-01 |
| 3  | B7      | 2018-03-10 |
| 4  | A3      | 2018-03-01 |
| 5  | A1      | 2018-03-01 |
+----+---------+------------+

PAYMENTS

+----+---------+--------+
| ID | SEGMENT | AMOUNT |
+----+---------+--------+
| 1  | B7      | 100    |
| 2  | B5      | 200    |
| 3  | A1      | 125    |
| 5  | A1      | 500    |
+----+---------+--------+

I'm having a hard time to do that. Someone could help me?

Upvotes: 0

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270371

Your data structure and question are a bit hard to follow. But I think I understand. You want customers contacted in both segments, and then to know how many paid by "a" segment.

If I assume that the contacts are only to one a segment, then I can do:

select c.a_segment, count(*) as num_received, count(p.id) as num_paid
from (select id, sum(case when segment like 'A%' then 1 else 0 end) as in_a,
             sum(case when segment like 'B%' then 1 else 0 end) as in_b,
             max(case when segment like 'A%' then segment end) as a_segment
      from contacts
      group by id
     ) c left join
    payments p
    on c.id = p.id
where c.in_b = 1 and c.in_a = 1
group by c.a_segment;

Upvotes: 1

Related Questions