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 10If 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?
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 10If 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) aWe're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.