having 2 tables
ckto primary key
cktno foreign key references circuit(cktno)
output required :
cktno statusid count( in) count( out)
0 0 0 1
1 1 2 1
2 0 0 1
3 0 1 1
This is called a Pivot. Exactly how to do it depends on your version of Oracle. Some different ways are shown in the forum FAQ:
Most of the examples you see will involve only 1 table. Start by pivoting your logs table, don't worry about the circuit table at first. Once you have a query that pivots the logs table, changing that to join to the other table will be easy.
Whenever you have a question, post your best attempt along with a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
Explain, using specific examples, how you get those results from that data.
Always say what version of Oracle you're using (e.g. 126.96.36.199.0).
See the forum FAQ: https://forums.oracle.com/message/9362002