proggR Posted July 6, 2011 Share Posted July 6, 2011 I have a query that returns only distinct records based on a number of columns in my table that I'm using for reporting. The only catch is that I also need to know the number of times each of these records occured that were ignored by using distinct. The table in question is a statistics table and I'm creating a report to display these statistics. I could leave out distinct and loop through the results checking if the record has been accounted for already and sum up each instance of its occurrence but I'm sure there's a way to do it with a single MySQL query. The following query gives me each interaction with a specific community by a specific user as well as the type of interaction. The other information I need is the number of times this type of interaction occurred per community entity. SELECT DISTINCT proxy_id, module, action_field, action_value, action FROM statistics WHERE module LIKE 'community:2:%' AND proxy_id = 1; I'm sure the answer is simpler than I am making it out to be but I haven't found any way to do it yet. Thanks in advance for your help. Link to comment https://forums.phpfreaks.com/topic/241257-retrieve-number-of-duplicates-while-using-distinct/ Share on other sites More sharing options...
proggR Posted July 6, 2011 Author Share Posted July 6, 2011 I figured it out. If I use GROUP BY instead of DISTINCT it gives me the results I want. SELECT COUNT(*) AS count, proxy_id, module, action_field, action_value, action FROM statistics WHERE module LIKE 'community:2:%' AND proxy_id = 1 GROUP BY proxy_id, module, action_field, action_value, action; Link to comment https://forums.phpfreaks.com/topic/241257-retrieve-number-of-duplicates-while-using-distinct/#findComment-1239263 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.