CODEWITHSUNDEEP
phpmysql
Stephen Adelakun
Stephen Adelakun

Reputation: 804

COUNTING mysql table rows based on 2 conditions from another table

I have two tables table1 and table2. table1 has columns id and table2_id while table2 has id and category. I need to count rows from table1 based on two separate values in table2.category containing value Regular or Special.

I have done this in two queries but I want to know if it is possible in a single sql. My queries are: 

"SELECT COUNT(t1.id) AS regular FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.t2_id = t2.id WHERE t2.category = 'Regular'";
"SELECT COUNT(t1.id) AS special FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.t2_id = pr.id WHERE t2.category = 'Special'";

Thanks.

EDIT

The second query JOIN should read ON t1.t2_id = t2.id and not ON t1.t2_id = pr.id. Sorry for the confusion that may have caused. Please update/edit your answers/comments accordingly.

Upvotes: 0

Views: 81

Answers (2)

Lajos Arpad
Lajos Arpad

Reputation: 76434

Instead of

"SELECT COUNT(t1.id) AS regular FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.t2_id = t2.id WHERE t2.category = 'Regular'";
"SELECT COUNT(t1.id) AS special FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.t2_id = pr.id WHERE t2.category = 'Special'";

you can do this:

select t2.category, count(t1.id)
from table1 t1
left outer join table2
on t1.t2_id = t2.id
group by t2.category
having t2.category in ('Regular', 'Special')

The suggested query groups the joined records, filters the groups and selects the category name and its count.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Move the Where condition to CASE statement and do the counting

Here is one way using Conditional Aggregate

SELECT
COUNT(case when t2.category = 'Regular' then t1.id end) AS Regular,
COUNT(case when t2.category = 'Special' then t1.id end) AS special 
FROM table1 t1 
INNER JOIN table2 t2 ON t1.t2_id = pr.id 
Where t2.category IN ('Regular','Special' )

Note : I have changed the LEFT JOIN to INNER JOIN because you want to count only when table2.category is 'Regular' or 'Special' so no use of LEFT JOIN here

Upvotes: 3

Related Questions