CODEWITHSUNDEEP
phpmysqlsql
Waheedullah Mujadidi
Waheedullah Mujadidi

Reputation: 11

MYSQL count different values of a row with distinct value of another row

Using the below query i'm able to get the count of male and female of each municipality but the problem is municipalities come in duplicate entries, I want all same municipalities to come in one row and all their male and females be counted all together. Someone please help me :(

I need the data of Gender and Training Location columns of the table2 and it is inner join with table1 because for each record of table1 I have many records in table2.

Please refer to the below picture of what i currently get through this query

Select 
  (Select count(*) from table2 where gender='Male' and table1.ID=table2.ID) AS Male, 
  (Select count(*) from table2 where gender='Female' and table1.ID=table2.ID) AS Female,  
  Training_Location 
from table2 
INNER JOIN table1 ON table1.id = table2.ID 
WHERE table1.Training_Start_Date BETWEEN '$StartDate' and '$EndDate' 
group by table1.ID

screen shot

Upvotes: 1

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

I think you want to remove the JOIN in the outer query:

select t1.id, t1.Training_Location 
       (Select count(*) from table2 t2 where t2.gender='Male' and t1.I D= t2.ID) AS Male, 
       (Select count(*) from tbl_ind1_sub tis where tis.gender ='Female' and t1.ID = tis.ID) AS Female,  
from table1 t1 ON table1.id = table2.ID 
where t1.Training_Start_Date between ? and ?;

It seems really strange to me that males and females would be in separate tables. But that is how your question is structured.

Note that no group by is needed in the outer query, assuming the id is unique (a reasonable assumption).

Also, do not munge queries with constants. Pass them into the query as parameters.

If you decide that the tables in the subquery are the same table, ask a new question. This question clearly has them as separate tables.

Upvotes: 1

Related Questions