table1
p_id pc_id
3 66
4 67
5 66
6 68
7 65
8 68
table2
pc_id pc_name
66 AB
67 Cd
65 Ef
68 Gh
I expect in result
Name Count
AB 2
Cd 1
Ef 1
Gh 2
I am using my code
SELECT COUNT(*), table2.pc_name
from table2
inner JOIN table1
ON
table2.pc_id = table1.p_id
You need not only to join tables, but to group the data by PC name as well:
SELECT table2.pc_name as PC_Name, COUNT(table1.p_id) as PC_Count
FROM table2 inner JOIN table1
ON table2.pc_id = table1.pc_id
GROUP BY table2.pc_name
Here is a link to SQL Fiddle
The above query is working assuming that PC name is unique. If not - you need to also use PC ID in SELECT and GROUP BY statements
Try this:
WITH x AS (SELECT 3 AS p_id, 66 AS pc_id FROM dual UNION ALL
SELECT 4 AS p_id, 67 AS pc_id FROM dual UNION ALL
SELECT 5 AS p_id, 66 AS pc_id FROM dual UNION ALL
SELECT 6 AS p_id, 68 AS pc_id FROM dual UNION ALL
SELECT 7 AS p_id, 65 AS pc_id FROM dual UNION ALL
SELECT 8 AS p_id, 68 AS pc_id FROM dual ),
y AS (SELECT 66 AS pc_id, 'AB' AS pc_name FROM dual UNION ALL
SELECT 67 AS pc_id, 'CD' AS pc_name FROM dual UNION ALL
SELECT 65 AS pc_id, 'EF' AS pc_name FROM dual UNION ALL
SELECT 68 AS pc_id, 'GH' AS pc_name FROM dual )
select distinct y.pc_name,
count(x.pc_id) over (partition by x.pc_id)
from x, y
where x.pc_id = y.pc_id ;
OUTPUT wil be:
PC_NAME COUNT(X.PC_ID)OVER(PARTITIONBYX.PC_ID)
------- --------------------------------------
AB 2
CD 1
GH 2
EF 1
Just use GROUP BY clause.. try this.
SELECT b.pc_name , COUNT(a.p_id) AS 'Count'
FROM table1 a INNER JOIN table2 b ON a.pc_id = b.pc_id
GROUP BY b.pc_name