CODEWITHSUNDEEP
mysqlsql
user4464505
user4464505

Reputation: 53

Select count with join in SQL query

How can I check how many products added by a,b,c,d respectively by using a query?

table1

admin_id admin_name
3           a
4           b
5           c  
6           d

table2

admin_id products
3          pDeal
3          pSeal    
4          pAeal
5          pZeal
6          pXeal    
3          pHeal
6          pPeal

Upvotes: 0

Views: 67

Answers (5)

Ninju
Ninju

Reputation: 2530

Use this

SELECT adm.admin_name,COUNT(pdr.products) as ProductCnt 
FROM table1 AS adm 
JOIN table2 AS pdr 
  ON adm.admin_id = pdr.admin_id 
GROUP BY adm.admin_id;

Upvotes: 1

shA.t
shA.t

Reputation: 16958

You can use an inner-select like this:

SELECT 
    table1.admin_name, 
    (SELECT COUNT(*) 
     FROM table2 
     WHERE table1.admin_id = table2.admin_id) As cnt
FROM
  table1;

Upvotes: 0

japzdivino
japzdivino

Reputation: 1746

Try this...

    SELECT a.admin_name, COUNT(b.products) as 'CountOfProducts' 
    FROM table1 a INNER JOIN table2 b ON a.admin_id = b.admin_id
    GROUP BY a.admin_name

Upvotes: 1

MaK
MaK

Reputation: 110

SELECT t1.admin_name, COUNT(t2.products)
FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.admin_id = t2.admin_id
WHERE 1
GROUP BY t2.admin_id

The LEFT JOIN will ensure the cases when table2 doesn't have any record for some admin of table 1.

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You need a simple JOIN and a COUNT query:

SELECT table1.admin_name, COUNT(*) as cnt
FROM
  table1 INNER JOIN table2
  ON table1.admin_id = table2.admin_id
GROUP BY
  table1.admin_name

Upvotes: 3

Related Questions