CODEWITHSUNDEEP
sqlpostgresql
thekucays
thekucays

Reputation: 618

Select data from each row query result

for example i have transaction table

id_transaction|description|id_operator
1             |foo        |21
2             |fooo       |21
1             |oof        |20

and the operator table like this 

id_operator|operator_name
20         |op1
21         |op2

how can i query how much transaction that has operator id 20 and 21? i want to retrieve data something like this

id_operator|count
20         |1
21         |2

i tried this query 

select count(*) from "transaction" where id_operator in (
    select id_operator from "operator" where "operator_name" like 'op%'
);

and this query

select count(DISTINCT(trt.id_operator)) from "transaction" trt
join "operator" mso on trt.id_operator = mso.id_operator
and mso."operator_name" like 'op%';

but these queries return all data from op1 and op2..and not for each operator.. how can i achieve this?

Upvotes: 1

Views: 764

Answers (2)

D-Shih
D-Shih

Reputation: 46219

Use group by

select mso.operator_name,count(trt.id_operator) 
FROM transaction trt
INNER JOIN operator mso on trt.id_operator = mso.id_operator
GROUP BY mso.operator_name;

SQLFiddle

Upvotes: 1

MKougiouris
MKougiouris

Reputation: 2861

You should look into GROUP BY clauses.

For your case i think you would need

select id_operator,count(id_transaction)
from "transaction"
group by id_operator

Upvotes: 1

Related Questions