CODEWITHSUNDEEP
phpmysqlsql
Paul
Paul

Reputation: 11746

mysql counts based on timestamps?

I'm trying to get a total count and a new count based on questions not answered.

I have two SQL calls that looks like so:

// retrieves all ids of questions and the timestamps when recorded
SELECT id, timestamp FROM `profile_questions` q WHERE q.user_id=5

// output:
id timestamp
-- ---------
1  1374677344
2  1374677514

// retrieves all answered questions
SELECT a.timestamp 
FROM `profile_answers` a 
LEFT JOIN profile_questions q ON q.id = a.question_id 
WHERE a.answered_by=5

Is there a way to combine the two statements to return a count of total questions and a count for new questions? Basically a count of any questions not answered?

Upvotes: 0

Views: 59

Answers (1)

juergen d
juergen d

Reputation: 204756

To count all questions not answered by a user do

SELECT count(q.id)
FROM `profile_questions` q 
LEFT JOIN profile_answers a ON q.id = a.question_id 
                            and q.user_id = a.answered_by
WHERE q.user_id = 5
and a.answered_by IS NULL

Upvotes: 2

Related Questions