[RESOLVED] Sum a Count

[Shawazi]

I am trying to find out how many repeat customers there are in a store. I want to print out a table that says something like

1 | 500
2 | 200
3 | 10

Meaning 500 customers ordered once, 200 twice, etc...

If I run this query:

 mysql_query("SELECT DISTINCT COUNT(*) as theCount FROM orders GROUP BY user_id ORDER BY theCount");

I get the numbers 1,2,3 for the different amount of times people ordered. If I don't use distinct I get 500 rows with 1, 200 rows with 2, etc.

Once I grab 1, 2, and 3, I am not sure what to do with them to get the next numbers. I can't do WHERE COUNT(*)=5 so that is throwing me off.

[Shawazi]

I eventually did this:

$ordered_res = my_query("SELECT DISTINCT COUNT(*) as theCount FROM orders GROUP BY user_id ORDER BY theCount");
			while ($ordered = mysql_fetch_array($ordered_res)) {
				$custs_res = my_query("SELECT COUNT(*) as theCount FROM orders GROUP BY user_id HAVING theCount=$ordered[theCount]");
				$custs = mysql_num_rows($custs_res);
				echo "
				<tr>
					<td>$custs</td>
					<td>$ordered[theCount]</td>
				</tr>
				";
			}

Why does this seem overly complicated to work?

[tomhath]

You have the count by user. Now you want a count of the counts:

SELECT theCount, count(*) as countOfTheCounts
FROM (SELECT COUNT(*) as theCount FROM orders GROUP BY user_id) as c
GROUP BY theCount
ORDER BY theCount

[Shawazi]

wicked