MySQL - finding the best buyer / the best buying country, etc.

Hi,

I'm working on a website using a PayPal, with a custom PHP/MySQL
(v4.0.17, very unlikely to be able to upgrade for this project) shopping
cart. This is only to let you know about the setup.

Now to my question:

I have a table with transaction informations, in which I have a line for
each transacion, containing a user identifier, their country of
residence, the amount of the transaction, and much more.

I want to be able to tell which user is the best buyer (or maybe the
best 10/20 buyers).
I also want to know which country buys the most.

I've made various pathetic attempts at mixing MAX() and SUM() but to no
avail.

I would really like to have no more than one query for each result, to
make it light on the server, and I also would appreciate to have little
PHP to write, as I think SQL is up to the task.

The amount field is set to varchar(6), but I could change it to a
numerical field if it helps.

I am not a total noob in MySQL, but I really use it with PHP for basic
tasks.

Any help or pointers will be appreciated, even an RTFM if you give me
the page number ;)

Sébastien

Re: MySQL - finding the best buyer / the best buying country, etc.

Sébastien wrote:
> Hi,
>
> I'm working on a website using a PayPal, with a custom PHP/MySQL
> (v4.0.17, very unlikely to be able to upgrade for this project) shopping
> cart. This is only to let you know about the setup.
>
> Now to my question:
>
> I have a table with transaction informations, in which I have a line for
> each transacion, containing a user identifier, their country of
> residence, the amount of the transaction, and much more.
>
> I want to be able to tell which user is the best buyer (or maybe the
> best 10/20 buyers).
> I also want to know which country buys the most.

Use GROUP BY
http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.ht ml



//Aho

Re: MySQL - finding the best buyer / the best buying country, etc.

J.O. Aho a écrit :
> Use GROUP BY
> http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.ht ml

Sweet! Thanks for the link!

I knew it had to be possible but this is really powerful. Works
perfectly with just one query each.

Thank You J.O. Aho,

Sébastien