aggregate combined with nonaggregate

[paubad]

I wish to perform a select in which I join two tables. I want to add an extra field telling me for each selected tuple from table 1 - how many tuples from table 2 were joined with it.

as follows:

table 1:

a %
b %
c +

table 2:

a 1
a 3
a 5
a 7
b 2
b 3
c 4
c 5
c 6

desired result:
a % 1 4
a % 3 4
a % 5 4
a % 7 4
b % 2 2
b % 3 2
c + 4 3
c + 5 3
c + 6 3

 

[r937]

select table1.column1  as table1_column1
     , table1.column2  as table1_column2
     , table2.column2  as table2_column2
     , ( select count(*)
           from table2
          where column1 
              = table1.column1 ) 
                       as table2_count
  from table1
left outer
  join table2
    on table2.column1 = table1.column1

r937.com | rudy.ca

 

[paubad]

I tried the above and this resulted in:

ORA-00933: SQL command not properly ended

 

[r937]

could you please show the actual query that produced the error

r937.com | rudy.ca

 

[paubad]

I ended up finding a workaround by restructuring my solution so I didn't need this select. Sorry to have posted a question that proved unnecessary. If you're curious I can still send you the query that produced the error but this won't be before monday.

 

[dnoeth]

If you're using Oracle then you have to get rid of the "AS" before an alias.

But instead of doing some complicated query just use the existing OLAP-functions:

select ...
count(*) over (partition by tab1.col1)
from tab1 join tab2 on tab1.col1 = tab2.col2

Dieter