MySQL RSS MySQL RSS

Need to sum distinct rows, not distinct values

Reply

•
•
Join Date: Feb 2002
Posts: 12,040
Reputation: cscgal is a glorious beacon of light cscgal is a glorious beacon of light cscgal is a glorious beacon of light cscgal is a glorious beacon of light cscgal is a glorious beacon of light cscgal is a glorious beacon of light 
Solved Threads: 127
Administrator
Staff Writer
cscgal's Avatar
cscgal cscgal is offline Offline
The Queen of DaniWeb

Need to sum distinct rows, not distinct values

 
0
  #1
Oct 2nd, 2009
I have a somewhat complicated select query which returns, in part, the following resultset (as an example):

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

If I were to do the following select:
MySQL Syntax (Toggle Plain Text) 
  1. 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.
Dani the Computer Science Gal
Follow my Twitter feed! twitter.com/daniweb
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 462
Reputation: tiger86 is an unknown quantity at this point 
Solved Threads: 10
tiger86's Avatar
tiger86 tiger86 is offline Offline
Posting Pro in Training

Re: Need to sum distinct rows, not distinct values

 
0
  #2
Oct 2nd, 2009
•
•
•
•
Originally Posted by cscgal View Post
I have a somewhat complicated select query which returns, in part, the following resultset (as an example):

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

If I were to do the following select:
MySQL Syntax (Toggle Plain Text) 
  1. 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!!
If I helped you I would appreciate it if you would give me some reputation.
read my actionscript to english blog
Currently developing what should be social network 2.0 offline.
Reply With Quote Quick reply to this message  
•
•
Join Date: Oct 2006
Posts: 2,833
Reputation: niek_e has a reputation beyond repute niek_e has a reputation beyond repute niek_e has a reputation beyond repute niek_e has a reputation beyond repute niek_e has a reputation beyond repute niek_e has a reputation beyond repute niek_e has a reputation beyond repute niek_e has a reputation beyond repute niek_e has a reputation beyond repute niek_e has a reputation beyond repute niek_e has a reputation beyond repute 
Solved Threads: 297
Moderator
Featured Poster
niek_e's Avatar
niek_e niek_e is offline Offline
Roasting Maven

Re: Need to sum distinct rows, not distinct values

 
0
  #3
Oct 2nd, 2009
•
•
•
•
Originally Posted by cscgal View Post
text Syntax (Toggle Plain Text) 
  1. threadid	views
  2. --------	-----
  3. 1	5
  4. 1	5
  5. 2	10
  6. 3	15
  7. 4	10
That looks like a database-design flaw to me

•
•
•
•
Originally Posted by cscgal View Post
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) 
  1. SELECT SUM(views) FROM (SELECT threadid, views FROM table_name GROUP BY threadid, views)
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2002
Posts: 12,040
Reputation: cscgal is a glorious beacon of light cscgal is a glorious beacon of light cscgal is a glorious beacon of light cscgal is a glorious beacon of light cscgal is a glorious beacon of light cscgal is a glorious beacon of light 
Solved Threads: 127
Administrator
Staff Writer
cscgal's Avatar
cscgal cscgal is offline Offline
The Queen of DaniWeb

Re: Need to sum distinct rows, not distinct values

 
0
  #4
Oct 2nd, 2009
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.
Dani the Computer Science Gal
Follow my Twitter feed! twitter.com/daniweb
Reply With Quote Quick reply to this message  
Reply

Tags
•
•
•
•
Tags
count, distinct, resultset, select

Quick Reply to Thread
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MySQL Forum
Thread Tools Search this Thread



array base box cfquery character check coldfusion conversion count counttheoccurenceofanintegerinthe10inputs dependent digit distinct exists frequency inputs insert integer interation javascript linked list media multipletables mysql mysqlquery occurence php query radix recursion resultset running select social sql statistics string view word
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC