count(*) with reference to another tbl 1

[puredesi5]

I have two tables; tbl_A & tbl_B
tbl_A has these columns (State_id, name, phone, etc.) and has 500,000 records.
tbl_B has these column (State_id, State_name, state_flag, etc.) and has 50 columns 1 for each state.
I want to get following listing of State id, State name and count for each state

State id, State Name, count(*)
1 Arizona 23,000
2 New Mexico 12,000
.
.
50 Hawaii 5,000.

I tried following but doesn't work (ORA00936: missing expression). Any idea?

select Distinct a.state_id, b.state_name, a.count(*)
from tbl_A a,
tbl_B b
where a.state_id = b.state_id
group by a.state_id

 

[lewisp]

Add b.state_name to your group by.

 

[lewisp]

select Distinct a.state_id, b.state_name, [COLOR=red]count(*)[/color] 
from tbl_A a, 
     tbl_B b
where a.state_id = b.state_id
group by a.state_id[COLOR=red],b.state_name[/color]

 

[puredesi5]

That worked. Thanks a lot Lewisp!
I really appreciate. I would have never thought of putting state name in a group by since it only happens one per record. But if it works, I won't argue.

 

[lewisp]

Any column that is not a summary must always be included in the group by.