Really can't figure this one out

[morphboy23]

Okay, so I've been trying for a while to figure this out, and haven't found a solution yet. 

 

The example is thus:  there is a 'questions' table, with a column 'idquestion' (among others that don't matter right now).  there is also an 'answers' table, which also has a 'idquestion' column which refers to the questions table one.  multiple rows in 'answers' might refer to the same idquestion.

 

I need to figure out a query that will select every questions.idquestion and the COUNT() of how many rows in 'answers' has that idquestion. 

So, for example,  if 3 answers point to idquestion=1, 2 answers point to idquestion=2, and there are 0 rows that point to idquestions 3 through 10, I want:

 

idquestion / COUNT()

1            /      3

2            /      2

3            /      0

...            /    ...

10          /      0

 

Thanks for any help

[bubblegum.anarchy]

SELECT idquestion, count(idquestion) AS counter
FROM answers
GROUP BY idquestions
ORDER BY counter DESC

[morphboy23]

Hm, it seemed to order the id's correctly, but the COUNT returned the same result for each id..

[morphboy23]

Hm after some tinkering I think I got it..

 

SELECT questions.idquestion AS id, (

SELECT COUNT( answers.idinquiry )
FROM answer
WHERE answer.idquestion = id
) AS counter
FROM question
GROUP BY question.idquestion
ORDER BY counter DESC

 

It works, but seems long..but maybe not? If anyone knows an easier/shorter way, please share!

[Illusion]

SELECT questions.idquestion AS id, COUNT( answers.idinquiry ) as a_count FROM questions,answers WHERE question.idquestion =answers.idquestion GROUP BY question.idquestion ORDER BY a_count DESC