Ad:

MySQL Discussion Thread
Unsolved
Views: 9632

Oct 2nd, 2009

Need to sum distinct rows, not distinct values

Expand Post »

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:

MySQL Syntax (Toggle Plain Text)
COUNT (DISTINCT threadid), SUM (DISTINCT views)
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?

Last edited by cscgal; Oct 2nd, 2009 at 1:49 am.

Similar Threads

Order By Group By Distinct Problem in MySQL
Count/Sum distinct problem in MS SQL
distinct Multi Union help in MS SQL
MS-Access SQL DISTINCT to find unique rows in MS Access and FileMaker Pro
SQL SUM problem in MS SQL

cscgal

Administrator

Staff Writer

Reputation Points: 1422

Solved Threads: 162

The Queen of DaniWeb

Offline

13,645 posts
since Feb 2002

Permalink

Oct 2nd, 2009

Re: Need to sum distinct rows, not distinct values

Click to Expand / Collapse Quote originally posted by cscgal ...

I have a somewhat complicated select query which returns, in part, the following resultset (as an example):
text Syntax (Toggle Plain Text)
threadid	views
--------	-----
1	5
1	5
2	10
3	15
4	10
threadid views -------- ----- 1 5 1 5 2 10 3 15 4 10
If I were to do the following select:
MySQL Syntax (Toggle Plain Text)
COUNT (DISTINCT threadid), SUM (DISTINCT views)
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!!

tiger86

Reputation Points: 48

Solved Threads: 11

Posting Pro

Offline

540 posts
since Feb 2008

Permalink

Oct 2nd, 2009

Re: Need to sum distinct rows, not distinct values

Click to Expand / Collapse Quote originally posted by cscgal ...

text Syntax (Toggle Plain Text)
threadid	views
--------	-----
1	5
1	5
2	10
3	15
4	10
threadid views -------- ----- 1 5 1 5 2 10 3 15 4 10

That looks like a database-design flaw to me

Click to Expand / Collapse Quote originally posted by cscgal ...

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:

mysql Syntax (Toggle Plain Text)
SELECT SUM(views) FROM (SELECT threadid, views FROM table_name GROUP BY threadid, views)
SELECT SUM(views) FROM (SELECT threadid, views FROM table_name GROUP BY threadid, views)

Nick Evan

Moderator

Featured Poster

Reputation Points: 4142

Solved Threads: 394

Industrious Poster

Offline

4,132 posts
since Oct 2006

Permalink

Oct 2nd, 2009

Re: Need to sum distinct rows, not distinct values

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.

cscgal

Administrator

Staff Writer

Reputation Points: 1422

Solved Threads: 162

The Queen of DaniWeb

Offline

13,645 posts
since Feb 2002

Permalink

Jul 26th, 2011

Re: Need to sum distinct rows, not distinct values

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

MySQL Syntax (Toggle Plain Text)
SELECT Count(ThreadId), Sum(Viewed) as View_Summary
FROM (SELECT DISTINCT ThreadId, Viewed FROM #tableViews) a
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

MySQL Syntax (Toggle Plain Text)
SELECT DISTINCT Count(ThreadId), Sum(Viewed) as View_Summary
FROM (SELECT * FROM #tableViews union Select * from #tableViews) a
Select Distinct Count(ThreadId), Sum(Viewed) as View_Summary
From (Select * from #tableViews union Select * from #tableViews) a

Last edited by Ezzaral; Jul 26th, 2011 at 7:25 pm. Reason: Added code tags. Please use them to format any code that you post.

Perez_Bottoms

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Jul 2011

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Need help with MySQL query

Next Thread in MySQL Forum Timeline: Sum columns over different ranges

DaniWeb Message

Cancel Changes

User Name
Password