关于创建特定查询的疑问[关闭]

Question

Ok I have a database where i generate a ticket code everytime a users buy an item. With this code they have the oportunity to win a price

To win the price they have to go to a register form, where the code is ask.. thats where the data is really generated. I ask for name, mail address, and other non important stuff

This part is working nice and properly. but i need to create o new function to detect the best buyers. So when they enter the code to participate , i ask for their mail, i have noticed, they actually enters the same mail each time they register a code. So this is the best way i think to select my best clients, and the most interested on promotions also.

So I think the best is to build a query to search into the mail row .. inside users table, and look for coincidences, and then this query can drop me a list of the most "repited" mails in DSC order,

is this posible, can i build a query like this? how?

Ok , thansk for the comments (why the downvote??) anyway

The structure is

ID | name | lastname | mail | phone | zipcode | birth |

And sorry at this moment i just CANT figure out a query thats why i ask. sorry for been a fool ,

Answer 1

How to order by the number of times a certain element occurs in a table

SELECT COUNT(*) AS top,mail,name lastname FROM users GROUP BY mail ORDER BY top DESC

This works because of aggregate functions

COUNT

Picks the number of rows in the individual elements of the group by (so suppose you ran count without a group by it would just return number of rows in the table)

GROUP BY

Groups the rows by common elements from the column(s) specified by the group by.

So in the above case it will group all the mails together and count the rows in each group.