This is my table structure
QUESTIONS:
id mediumint(4) unsigned
dept_id smallint (3)
posted_on datetime
user_id mediumint(4)
is_answered enum ('Y','N','C','F')
question varchar(255)
answer mediumtext
There is another table called departments where the department names are entered, the dept_id in this table refers to that tables rowid.
There is another table called users where the professor's details of each department to whom the questions get posted are entered, the [user_id] in this table refers to that tables rowid.
There are questions posted to each of these departments which are stored in the [QUESTIONS] table described above. When a new question gets posted the [is_answered] field is set to 'N', when it gets answered the value gets updated to 'Y' and if the question was read and cancelled the value gets updated to 'C', if the question was forwarded to another department then value gets updated to 'F'
When describing the stats for this institution I need to show the list as below
in one line:
Department name, number of questions posted, number of questions answered,number of questions cancelled, number of questions forwarded, Maximum questions answerd by which user[user_id]
How can I accomplish this in one query?
Is it possible in one query or what else can I do ?
QUESTIONS:
id mediumint(4) unsigned
dept_id smallint (3)
posted_on datetime
user_id mediumint(4)
is_answered enum ('Y','N','C','F')
question varchar(255)
answer mediumtext
There is another table called departments where the department names are entered, the dept_id in this table refers to that tables rowid.
There is another table called users where the professor's details of each department to whom the questions get posted are entered, the [user_id] in this table refers to that tables rowid.
There are questions posted to each of these departments which are stored in the [QUESTIONS] table described above. When a new question gets posted the [is_answered] field is set to 'N', when it gets answered the value gets updated to 'Y' and if the question was read and cancelled the value gets updated to 'C', if the question was forwarded to another department then value gets updated to 'F'
When describing the stats for this institution I need to show the list as below
in one line:
Department name, number of questions posted, number of questions answered,number of questions cancelled, number of questions forwarded, Maximum questions answerd by which user[user_id]
How can I accomplish this in one query?
Is it possible in one query or what else can I do ?