CODEWITHSUNDEEP
mysqlsql
John Santiago
John Santiago

Reputation: 109

MySQL: Counting number of records in a table using a value in another table

I have two tables in my database. One is "TBL_USERS" and the other "TBL_RESPONDENTS".

TBL_USERS Columns
USR_NUM - Primary key, Integer(15)
NAME - Varchar(70)

TBL_RESPONDENTS Columns
RSPONDNT_NUM - Primary key, Integer(15)
SURVYR_NUM - Integer(15)
QN_NUMBR - Integer(15)

I am making a survey encoding application where one user is given a filled up interview form with a unique questionnaire number (QN_NUMBR). The value in the SURVYR_NUM column is the user who entered the form into the database (USR_NUM).

I have difficulty creating a query that will count how many forms that each user has entered into the database.

The output should be like this:

USER ID    NAME     QN FORMS ENETERED
  1001    Mike          3

This is my inital query:

select tbl_users.name, tbl_users.usr_num, tbl_intrvw.qn_numbr from tbl_users inner join tbl_intrvw on tbl_users.usr_num = tbl_intrvw.survyr_num

and this is the output:

 name     usr_num    qn_numbr
 Mike     1001        2083
 Mike     1001        8102
 Mike     1001        1020

Upvotes: 2

Views: 57

Answers (1)

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

SELECT tbl_users.usr_num AS UserID, tbl_users.name AS UserName, COUNT(*) as QN_FORMS_ENETERED
FROM tbl_users INNER JOIN tbl_intrvw 
        ON tbl_users.usr_num = tbl_intrvw.survyr_num
GROUP BY tbl_users.usr_num,tbl_users.name

Upvotes: 1

Related Questions