CODEWITHSUNDEEP
sqlsql-servert-sql
Jeremy F.
Jeremy F.

Reputation: 1880

SQL: Count two different columns from two different tables

I am trying to get the distinct counts for the resource column of two different tables, then show the comparison for each project ID. Right now, this query gives me the same count values for both tables.

select 
    t1.PRJCT_ID,
    count(t1.RSRC_ID) as TBL1_RSRC_CNT,
    t2.PRJCT_ID,
    count(t2.RSRC_ID) as TBL2_RSRC_CNT
from
    DATA_TABLE_1 t1
LEFT OUTER JOIN 
    DATA_TABLE_2 t2 on t1.PRJCT_ID = t2.PRJCT_ID
GROUP BY
    t1.PRJCT_ID, t2.PRJCT_ID
order by 1

Upvotes: 1

Views: 15104

Answers (2)

Blindy
Blindy

Reputation: 67544

Of course you're going to get the same count like that, you're counting the columns of the same table (which is made by a join, granted, but it's still a rectangular table).

What you want to do is use subqueries. First get a list of every project id (from a table, or an union of parsing both tables in question, but that's a sign of bad database normalization), then query the tables independently for their count:

select p.ID,
  (select count(*) from DATA_TABLE_1 t1 where t1.ID=p.ID) Count1,
  (select count(*) from DATA_TABLE_2 t2 where t2.ID=p.ID) Count2
from projects p

Upvotes: 6

Laurent PerrucheJ
Laurent PerrucheJ

Reputation: 501

Note: I suppose you a PROJECT table somewhere. I think the best way to do this is with two subqueries. This way, you will have all your projects, even those without any resources. Something like: 

SELECT 
    p.PRJCT_ID,
    ( SELECT COUNT(*) FROM DATA_TABLE_1 t1 WHERE t1.PRJCT_ID = p.PRJCT_ID ) AS TBL1_RSRC_CNT,
    ( SELECT COUNT(*) FROM DATA_TABLE_2 t2 WHERE t2.PRJCT_ID = p.PRJCT_ID ) AS TBL2_RSRC_CNT
FROM PROJECT p
ORDER BY p.PRJCT_ID

Upvotes: 1

Related Questions