CODEWITHSUNDEEP
oracle-databaseselectcountsubquery
mikezang
mikezang

Reputation: 2489

Select Count from sub table in Oracle

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'

Upvotes: 0

Views: 378

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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;

enter image description here

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.

Upvotes: 2

Related Questions