-1

Is there any methods to get the row count of the table which is related to the specified condition.?

here is my query

select * 
from table2 t2 
   inner join t1 on t2.table1_id = t1._id where t1.user_id=1

T1

| _id | user_id  |
==================
|  1  |   1      |
|  2  |   1      |
|  3  |   1      |
|  4  |   2      |
|  5  |   2      |
|  6  |   2      |
|  7  |   1      |

T2:

| _id | table1_id|
====================
|  1  |   1      |
|  2  |   1      |
|  3  |   2      |
|  4  |   2      |
|  5  |   2      |
|  6  |   6      |
|  7  |   1      |
|  8  |   6      |
|  9  |   7      |

Output:

| _id | table1_id|
====================
|  1  |   1      |
|  2  |   1      |
|  3  |   2      |
|  4  |   2      |
|  5  |   2      |
|  7  |   1      |
|  9  |   7      |

I have to display a number of rows present in t1 and t2 which are related to each other based on the user id

According to the above example, my expected result should be t1's count = 4 and t2 count = 7 If anyone knows guide me here.

  • 2
    I don't understand what you mean with "length". The number of rows? – a_horse_with_no_name Sep 24 '18 at 11:41
  • @a_horse_with_no_name yes, the number of rows. – Schüler Sep 24 '18 at 12:04
  • 1
    I'm not sure if I understand your question but try to put a COUNT() by T1 id to get the T2 count, for T1 just use a trivial query – jean Sep 24 '18 at 12:05
  • I still don't think this is quite clear - you say you want a count of rows, but you show the rows matching user_id = 1 from table2 as your output. Please clarify your desired results. – RDFozz Sep 25 '18 at 14:02
3

The only way I can think of, is something like this:

select t1._id as t1_id, 
       t2.table1_id,
       t1.t1_count,
       count(*) filter (where t2.table1_id = t1._id) over () as t2_count
from (
  select t1.*, 
         count(*) over () t1_count
  from t1 
  where t1.user_id = 1
) t1 
  join t2 on t2.table1_id = t1._id;

It is necessary to push the condition where t1.user_id = 1 into the first derived table in order to get the count of rows from that table correctly. Otherwise count(*) would count all rows in that table, not just those for user_id = 1.

I didn't find a way to avoid repeating the join condition for the counting of the rows coming from t2


Another (most probably slower) way would be:

select t1._id as t1_id, 
       t2.table1_id,
       (select count(*) from t1 x where x.user_id = t1.user_id) as t1_count,
       count(*) filter (where t2.table1_id = t1._id) over () as t2_count
from t1 
  join t2 on t2.table1_id = t1._id
where t1.user_id = 1;
  • Thanks a ton this works for me, I failed to elaborate my question but surely i will improve :) – Schüler Sep 25 '18 at 4:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.