CODEWITHSUNDEEP
sqlselectjoincount
user861587
user861587

Reputation: 69

SQL Join Question

I have 2 tables like this :

category_table:

data_id  cat_id
      1       1
      1       2
      3       1
      3       3
      4       5
      4       6

data_table:

data_id  example_data
      1             x
      2             y
      3             m
      4             i

what I need is to count number of data_table records that are grouped with category 1 AND 2

Upvotes: 0

Views: 262

Answers (4)

shijuse
shijuse

Reputation: 291

 select Cat_id,COUNT(*) [number of data_table] from data_table
 inner join category_table on category_table.data_id = data_table.data_id 
 group by Cat_id

OR

  select Cat_id,COUNT(*) [number of data_table] from category_table
  group by Cat_id

Upvotes: 0

SouravM
SouravM

Reputation: 209

SELECT COUNT(CAT_ID) 
FROM CATEGORY_TABLE 
JOIN DATA_TABLE ON CATEGORY_TABLE.CAT_ID=DATA_TABLE.DATA_ID 
WHERE CATEGORY_TABLE.CAT_ID IN (1,2)

Upvotes: 0

Derek
Derek

Reputation: 23318

Edit: After re-reading your question, is this what you're looking for? A count of elements from data_table where there exists an entry in the category_table for both categories 1 and 2?

select
    count(*)
from
    data_table d
    inner join category_table c1
        on  d.data_id = c1.data_id
    inner join category_table c2
        on  d.data_id = c2.data_id
where
    c1.cat_id = 1
    c2.cat_id = 2

(old answer below this...)

If you want a straight count of all records:

select
    count(*)
from
    data_table d
    inner join category_table c
        on  d.data_id = c.data_id
where
    c.cat_id in (1, 2)

If you want a count for each category:

select
    c.cat_id, count(*)
from
    data_table d
    inner join category_table c
        on  d.data_id = c.data_id
where
    c.cat_id in (1, 2)
group by
    c.cat_id

Upvotes: 2

Richard Inglis
Richard Inglis

Reputation: 5958

If you want to count the rows in data_table whose data_id is associated (in category_table) with a cat_id of 1 and also with a cat_id of 2...

select count(*) from data_table
where data_id in 
 (select data_id from category_table as ct1 where ct1.cat_id=1 and exists
  (select * from category_table as ct2 where ct2.cat_id=1 and ct2.data_id=ct1.data_id)
  )

In your case, this will return a value of 1, for the first row of the data_table (1 : x), since that data_id (1) is the only one with a row in category_table for which cat_id=1 and also a row for which cat_id=2.

Upvotes: 0

Related Questions