Jump to content

Retrieve number of duplicates while using distinct


proggR

Recommended Posts

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.

 

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;

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.