CODEWITHSUNDEEP
sqlcount
JasonMetr
JasonMetr

Reputation: 33

Trouble writing SQL Query using COUNT

enter image description here

I am trying to write an SQL Query where I print the name, gender from table 1 along with the number of participants. The id tags in Table 1 and Table 2 are the same, so my idea is to group the participants via id tags and then count them.

$sql = 'SELECT T1.name, T1.gender, COUNT(T2.Participant_name) AS participant_count
                FROM Table1 T1, Table2 T2 
                WHERE T1.Id = T2.Id
                GROUP T2.Id';

The logic makes sense to me, however, nothing seems to be printing and I’m unsure where I have gone wrong.

Desired Output:

Upvotes: 0

Views: 39

Answers (2)

Fahmi
Fahmi

Reputation: 37473

Try below group by with two columns T1.name, T1.gender

SELECT T1.name, T1.gender, COUNT(T2.Participant_name) AS participant_count
                FROM Table1 T1 inner join Table2 T2 
                on T1.Id = T2.Id
                GROUP T1.name, T1.gender

OR you can try this

select T1.name, T1.gender, T1.age,participant_count from
Table1 T1 inner join
( SELECT ID,COUNT(Participant_name) AS participant_count
                    FROM Table2  
                    GROUP ID
)T2 on T1.Id = T2.Id

Upvotes: 1

Pelin
Pelin

Reputation: 966

You should change the group statement, cause when you group by the column that you count, result would be always 1

SELECT T1.name, T1.gender, COUNT(T2.Participant_name) AS participant_count
                FROM Table1 T1, Table2 T2 
                WHERE T1.Id = T2.Id
                GROUP BY T1.name, T1.gender

Upvotes: 0

Related Questions