Need to sum distinct rows, not distinct values

I have a somewhat complicated select query which returns, in part, the following resultset (as an example):

threadid	views
--------	-----
1	5
1	5
2	10
3	15
4	10

If I were to do the following select:

COUNT (DISTINCT threadid), SUM (DISTINCT views)

then it would return 4, 30. (5 + 10 + 15)

I am looking for a select where I want to find the SUM of views for distinct threadids, not distinct views. Something that would return 4, 40. (5 + 10 + 15 + 10) Is there something I can do?

Hey Dani, I saw your post via twitter. I think that mysql_num_rows maybe able to help you out with this problem. Here is a link to a detailed explanation and examples http://us2.php.net/manual/en/function.mysql-num-rows.php
I hope that helps!!

threadid	views
--------	-----
1	5
1	5
2	10
3	15
4	10

That looks like a database-design flaw to me ;)

I am looking for a select where I want to find the SUM of views for distinct threadids, not distinct views. Something that would return 4, 40. (5 + 10 + 15 + 10) Is there something I can do?

Haven't MySQL'd in a while, but I think something like this might do the trick:

SELECT SUM(views) FROM (SELECT threadid, views FROM table_name GROUP BY threadid, views)

Hey guys! Thanks for the assistance. Right now I have it working a very, very BAD way (Basically I have it broken up into one sql query that loops through, with a second sql query within the loop.) It gives the right data but it's obviously not ideal.

It's 3 am and I've lost every ounce of concentration in me, so I will check out your solutions tomorrow. I tried playing around with subqueries earlier, but I got stuck because the example I'm giving you is a huge oversimplification of a piece of the resultset ... there are a whole bunch of joins and where clauses and I wasn't sure exactly how much needed to be "duplicated" in the subquery.

Also ... about it being a db design flaw ... This is really just a single crazy behind-the-scenes page to pull stats history on traffic to the various sections of the site, so the database wasn't designed around it.

--Query that selects the distinct rows using a subquery and then does the aggregation

Select Count(ThreadId), Sum(Viewed) as View_Summary
From (Select Distinct ThreadId, Viewed From #tableViews) a

--OR--

--Query that eliminates the duplicate rows using the union clause and then does the aggregation

Select Distinct Count(ThreadId), Sum(Viewed) as View_Summary
From (Select * from #tableViews union Select * from #tableViews) a