Counting quantities in a database

This site is best viewed in a modern browser with JavaScript enabled.

themonkey40

My company is hosting a sales promotion and all the data for each sale is kept in a mysql database. We want to see who the top salesperson is, by counting the number of sales of each participant, then post the one with the highest total.

I am not even sure where to start with this using PHP and MySql. Can anybody help me get started.

Mindzai

You will want to look into the SQL 'GROUP BY', 'ORDER BY' and 'LIMIT' statements. They will be what you need to select the top salesperson. Impossible to give any specific help without seeing some code or knowing your db structure though.

themonkey40

Here is my table structure for the table:

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `po_number` varchar(7) DEFAULT NULL,
  `product` varchar(55) DEFAULT NULL,
  `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `shipping_firstname` varchar(55) DEFAULT NULL,
  `shipping_lastname` varchar(55) DEFAULT NULL,
  `shipping_company` varchar(255) DEFAULT NULL,
  `shipping_address1` varchar(255) DEFAULT NULL,
  `shipping_address2` varchar(255) DEFAULT NULL,
  `shipping_city` varchar(255) DEFAULT NULL,
  `shipping_state` varchar(255) DEFAULT NULL,
  `shipping_zip` varchar(255) DEFAULT NULL,
  `salesperson` varchar(55) DEFAULT NULL,
  `salesperson_id` int(11) DEFAULT NULL,
  `distributor` varchar(255) DEFAULT NULL,
  `distributor_address1` varchar(255) DEFAULT NULL,
  `distributor_address2` varchar(255) DEFAULT NULL,
  `distributor_city` varchar(255) DEFAULT NULL,
  `distributor_state` varchar(2) DEFAULT NULL,
  `distributor_zip` int(11) DEFAULT NULL,
  `distributor_phone` varchar(15) DEFAULT NULL,
  `distributor_id` int(11) DEFAULT NULL,
  `returned` int(11) DEFAULT NULL,
  `mra_number` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=latin1

I have been reading up on 'Group By', 'Order by' and 'Limit' but would really appreciate an example to help we put it all together. Thanks in advance.