CODEWITHSUNDEEP
sql
MartinW
MartinW

Reputation: 5041

How to join and count the results of two SQL queries on the same table

I have one table that contains all kind of metadata about customers. It is structured like this:

customer_id, metadata_id, text_value

I want to count all customers that have a specific text_value for metadata_id='metadata_1' and for whom an entry for metadata_id='metadata_2' exists.

customer_id metadata_id text_value
customer_1 metadata_1 yes!
customer_1 metadata_2 random value
customer_2 metadata_1 yes!
customer_2 metadata_2 never mind
customer_3 metadata_1 no!
customer_3 metadata_2 another value
customer_4 metadata_1 yes!

I can easily get the two parts:

SELECT customer_id
FROM my_table
WHERE metadata_id='metadata_1' AND text_value='yes!';

SELECT customer_id
FROM my_table
WHERE metadata_id='metadata_2';

But how do I combine them and count them?

Here customer_1 and customer_2 fulfil both conditions. So the answer for the example would be 2.

Upvotes: 0

Views: 89

Answers (5)

DannySlor
DannySlor

Reputation: 4620

We mark the customer_id that follows condition one. Then we mark the customer_id that follows condition two and then we count the customer_id we have left.

select  count(distinct customer_id) as cnt
from   (
       select  *
               ,count(case when metadata_id = 'metadata_1' and text_value = 'yes!' then 1 end) over(partition by customer_id) as mrk1 
               ,count(case when metadata_id = 'metadata_2' then 1 end) over(partition by customer_id) as mrk2
       from    t
       ) t
where  mrk1 > 0 
and    mrk2 > 0
cnt
2

Fiddle

Upvotes: 2

MHA
MHA

Reputation: 89

you can use OR operator and merge your conditions.

SELECT
    COUNT(*)
FROM
    (
        SELECT
            customer_id
        FROM
            my_table
        WHERE
            ( ( metadata_id= 'metadata_1' AND text_value= 'yes!' )
              OR metadata_id= 'metadata_2' )
        GROUP BY
            customer_id
        HAVING
            COUNT(*) > 1
    );

Upvotes: 0

ahmed
ahmed

Reputation: 9191

You can use EXISTS operator as the following:

SELECT COUNT(*) AS CNT
FROM my_table T
WHERE metadata_id='metadata_1' AND text_value='yes!'
AND EXISTS(SELECT 1 FROM my_table D WHERE D.customer_id=T.customer_id AND D.metadata_id='metadata_2')

If there is a duplicate ids and and you want to count only distinct ids you can use COUNT(DISTINCT customer_id).

And if you want to get the customer ids:

SELECT customer_id
FROM my_table T
WHERE metadata_id='metadata_1' AND text_value='yes!'
AND EXISTS(SELECT 1 FROM my_table D WHERE D.customer_id=T.customer_id AND D.metadata_id='metadata_2')

See a demo.

Upvotes: 2

Razvan
Razvan

Reputation: 126

I think this should do it

;WITH
  T1
  AS
  (
    SELECT customer_id
    FROM my_table
    WHERE metadata_id='metadata_1' AND text_value='yes!'
  ),
  T2
  AS
  (
    SELECT customer_id
    FROM my_table
    WHERE metadata_id='metadata_2'
  )
  SELECT COUNT(1)
  FROM T1
    INNER JOIN
    T2 ON T1.customer_id = T2.customer_id

Upvotes: 1

xan
xan

Reputation: 418

A UNION should work I think

SELECT COUNT(customer_id)
FROM (
        SELECT customer_id
        FROM my_table
        WHERE metadata_id = 'metadata_1'
            AND text_value = 'yes!'
        UNION
        SELECT customer_id
        FROM my_table
        WHERE metadata_id = 'metadata_2'
    ) alpha;

Upvotes: 0

Related Questions