Problem with a MySQL query with JOIN relation and COUNT

I have a table where I store the relations开发者_JAVA百科 of users with a projects. The projects can be grouped with a reftecid and I need to obtain the relation of users - duration with a simple query.

Here is my table rel_user_proj:

userid / projectid / role / duration (default NULL) / inactuserid (default NULL) / inactstamp (default NULL)

Then I'm trying this:

SELECT COUNT( * ) AS total, duration
FROM projects p
JOIN rel_user_proj rup ON p.projectid = rup.projectid
WHERE p.reftecid=26
AND rup.duration IS NOT NULL
GROUP BY duration
LIMIT 0 , 30

What I finally need is a list like:

• total: 1, duration: 3
• total: 3, duration: 12
• total: 2, duration: 1

In my current query, what I get is:

• total: 58, duration: 1 <- NOT VALID
• total: 1, duration: 9 <- VALID

The only projectid with reftecid=26 is 2010202, if I do:

SELECT * FROM `rel_user_proj` WHERE projectid=2010202

I get a UNIQUE row userid=49 with the correct duration. I do not understand where the 58 others are comming.

Can anybody help me?

Thank you in advance!

Try this:

SELECT COUNT( * ) AS total, duration
FROM projects p
JOIN rel_user_proj rup ON p.projectid = rup.projectid
WHERE p.reftecid=26
AND rup.duration IS NOT NULL
GROUP BY reftecid
LIMIT 0 , 30

I am not entirely sure how the duration plays a role in the above but if you need the sum of all the durations then you can do something like this:

SELECT COUNT( * ) AS total, sum(duration) AS total_duration
FROM projects p
JOIN rel_user_proj rup ON p.projectid = rup.projectid
WHERE p.reftecid=26
AND rup.duration IS NOT NULL
GROUP BY reftecid
LIMIT 0 , 30