Select Count from sub table in Oracle

Question

I have two tables as below with three keys and sub table has multiple records on key no31:

TableA: no1,no2,no31
        no1,no2,n032
TableB: no1,no2,n031,file311
        no1,no2,n031,file312
        no1,no2,n031,file313
        no1,no2,n032,file321
        no1,no2,n032,file322

I want to select result as below to count records on sub table with same three keys

result: no1, no2, no31, 3
        no1, no2, no32, 2

I tried SQL as below, but I got error ORA-00904: "A"."ARTICLE_NO": "%s: invalid identifier", what can I do?

SELECT A.no1, A.no2, A.no3, P.PHOTO_COUNT
FROM TableA A,
   (SELECT COUNT(*) AS PHOTO_COUNT
    FROM TableB
    WHERE no1 = 'param1' AND no2 = 'param2' AND no3 = A.no3) P
WHERE A.no1 = 'param1' AND A.no2 = 'param2'

Tim Biegeleisen · Accepted Answer · 2019-05-15 05:18:55Z

2

Just use a left join:

SELECT a.no1, a.no2, a.no3, COUNT(b.no1) AS PHOTO_COUNT
FROM TableA a
LEFT JOIN TableB b
    ON a.no1 = b.no1 AND a.no2 = b.no2 AND a.no3 = b.no3
GROUP BY a.no1, a.no2, a.no3;

Demo

The call to COUNT(b.no1), which counts a column in TableB, is significant. We count a TableB column because, in the case that a given group in TableA does not match to even a single record in TableB, COUNT would return zero, because NULL values are ignored.

edited May 15, 2019 at 5:18

answered May 15, 2019 at 5:12

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select Count from sub table in Oracle

1 Answer 1

Demo

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related