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 ,
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.
Just count by row and group by email:
SELECT COUNT(*) AS top_buyers, mail FROM users GROUP BY mail ORDER BY top_buyers DESC
something like this should work.
select email, count(*) as num_entries from emails group by email order by num_entries desc;