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?
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
Use count distinct
SELECT COUNT(distinct table1.act) FROM table1
INNER JOIN table2 ON table1.act = table2.act
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:
SELECT COUNT(table2.act) FROM table1
INNER JOIN table2 ON table1.act = table2.act
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
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;
table1 and table2 (and not T1 and T2) and the name of the column is act (and not emp_id) and I don't understand why the BETWEEN operator is required. Maybe you meant to post this answer to a different question?