CODEWITHSUNDEEP
sqlcount
Steve Payne
Steve Payne

Reputation: 633

SQL Count from INNER JOIN

How can I select the count from an inner join with grouping?

SELECT COUNT(table1.act) FROM table1
INNER JOIN table2 ON table1.act = table2.act
GROUP BY table1.act

This will return the count of act's found in table2.

Adding

 SELECT table1.act, COUNT(table1.act) AS test

Returns

act     test
------- ----
17682   3
17679   3
17677   3
11636   1
11505   1

I want to receive the count of act's found total.

So I want to get 5. Can you help?

Upvotes: 27

Views: 145175

Answers (6)

Uday Kumar
Uday Kumar

Reputation: 1

SELECT COUNT(*) AS total_records
FROM T1
INNER JOIN T2 ON T1.emp_id = T2.emp_id
WHERE T1.emp_id BETWEEN 1 AND 50;

Upvotes: -3

SendETHToThisAddress
SendETHToThisAddress

Reputation: 3774

Wrap the entire query with a SELECT COUNT statement as shown in the answer from Mosty Mostacho, just wanted to post this answer with an as statement since normally it's better to return results with a column title.

SELECT COUNT(*) as FilteredActs FROM (
    SELECT COUNT(table1.act) AS actCount FROM table1
    INNER JOIN table2 ON table1.act = table2.act
    GROUP BY table1.act
) T

Results:

output results

Upvotes: 2

Sean M
Sean M

Reputation: 626

if you just want the result count,

SELECT     COUNT(1)
FROM       table1
INNER JOIN table2 ON table1.act = table2.act
GROUP BY   table1.act

that should give you

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

You can wrap that query into another query:

SELECT COUNT(*) FROM (
    SELECT COUNT(table1.act) AS actCount FROM table1
    INNER JOIN table2 ON table1.act = table2.act
    GROUP BY table1.act
) t

Upvotes: 38

ingo
ingo

Reputation: 5579

Use count distinct

SELECT COUNT(distinct table1.act) FROM table1
INNER JOIN table2 ON table1.act = table2.act

Upvotes: 11

Nick
Nick

Reputation: 1128

SELECT COUNT(table2.act) FROM table1
INNER JOIN table2 ON table1.act = table2.act

Upvotes: 1

Related Questions