Multiple Records, single output

[strongpot]

Hello,
Self taught guy here. Thanks for reading my post.

I've made a app to track the sales of floating rubber ducks for a duck race.

I have a database which contains data similar to this:

id   soldby    package
------------------------
1    John      6
2    John      25
3    John      1
4    John      6
5    John      25
6    John      1
7    Tammy     6
8    Tammy     25
9    Tammy     1
10   Tammy     1
11   Tammy     6
12   Tammy     25
13   Tammy     25

The number in the package field refers to the number of ducks purchased. Each package 1,6 and 25 has a dollar amount $5, $25 and $100. I need to output data for each sales person containing total number of ducks sold and the full dollar amount. I'd like the output to be similar to this format:

Sales Person    Total Ducks Sold     Total Dollars
John            64                   260
Tammy           58                   360

Please offer help on how to query the db to unify all the numbers with the correlating name so I arrive at something similar the the above. I'm not sure where to start as I've never had this challenge before.

[pritaeas]

Using GROUP BY will easily SUM the package, but since you need to calculate the total amount, you can use a calculated column using a CASE.

[strongpot]

Thanks, I'm still confused, is there any more info you can give?

[cereal]

You will probably need to perform a subquery in order to get the results of the cases, and then sum the results in the main query, this should work:

SELECT s.soldby AS 'sales person', SUM(sub.packages) AS packages, SUM(sub.sales) AS total FROM sales AS s, (SELECT id, soldby, CASE WHEN package = 1 THEN count(package)*5 WHEN package = 6 THEN COUNT(package)*25 WHEN package = 25 THEN COUNT(package)*100 END AS sales, SUM(package) AS packages FROM sales GROUP BY soldby, package) AS sub WHERE s.id = sub.id GROUP BY s.soldby;

Reference table for this example:

CREATE TABLE `sales` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `soldby` varchar(255) DEFAULT NULL,
  `package` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Reference link: http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html

But I'm wondering if there is a better method to achieve this. Maybe a procedure?

[pritaeas]

Just use the SUM(CASE) in the SELECT.

[strongpot]

works great any tips on getting this to echo properly?

[pritaeas]

Show your code.

[strongpot]

Ok after reviewing I realize I screwed up a bit. Let me update my needs more accurately below. We were definately on the right track with your above suggestions and they worked great.

id  soldby       buyerfirst   buyerlast   email                package  
1   Salesman 1   Dick         Jones       [e-mail]    25          
2   Salesman 1   Dick         Jones       [e-mail]    25          
3   Salesman 1   Dick         Jones       [e-mail]    25          
4   Salesman 1   Dick         Jones       [e-mail]    25          
5   Salesman 1   Dick         Jones       [e-mail]    25         
6   Salesman 1   Dick         Jones       [e-mail]    25         
7   Salesman 1   Dick         Jones       [e-mail]    25        
8   Salesman 1   Dick         Jones       [e-mail]    25          
9   Salesman 1   Dick         Jones       [e-mail]    25          
10  Salesman 1   Dick         Jones       [e-mail]    25           
11  Salesman 1   Dick         Jones       [e-mail]    25           
12  Salesman 1   Dick         Jones       [e-mail]    25          
13  Salesman 1   Dick         Jones       [e-mail]    25          
14  Salesman 1   Dick         Jones       [e-mail]    25          
15  Salesman 1   Dick         Jones       [e-mail]    25         
16  Salesman 1   Dick         Jones       [e-mail]    25         
17  Salesman 1   Dick         Jones       [e-mail]    25         
18  Salesman 1   Dick         Jones       [e-mail]    25          
19  Salesman 1   Dick         Jones       [e-mail]    25          
20  Salesman 1   Dick         Jones       [e-mail]    25           
21  Salesman 1   Dick         Jones       [e-mail]    25          
22  Salesman 1   Dick         Jones       [e-mail]    25           
23  Salesman 1   Dick         Jones       [e-mail]    25          
24  Salesman 1   Dick         Jones       [e-mail]    25          
25  Salesman 1   D            Jones       [e-mail]    25           
26  Salesman 2   Clarence     Boddicker   [e-mail]        6  
27  Salesman 2   Clarence     Boddicker   [e-mail]        6
28  Salesman 2   Clarence     Boddicker   [e-mail]        6
29  Salesman 2   Clarence     Boddicker   [e-mail]        6
30  Salesman 2   Clarence     Boddicker   [e-mail]        6
31  Salesman 2   Clarence     Boddicker   [e-mail]        6 
32  Salesman 3   Anne         Lewis       [e-mail]       1 
33  Salesman 1   Alex         Murphy      [e-mail]       1
34  Salesman 1   Fred         Friendly    [e-mail]     1 

There are three packages of ducks available to buy. 1 duck is $5, 6 ducks is $25 and 25 ducks is $100.

Each Duck purchased is numbered and that number corresponds to the id column.

Each time a sale is made the sale is entered into the db as a new record for each duck sold. Above, Dick Jones bought 25 ducks so each duck is its own record in the db (Dick Jones did not buy 25 orders of 25 ducks).

Clarence Boddicker bought 6 ducks, Anne, Alex and Fred all bought one duck. Each had a different sales person although it could just as easily been the same sales person.

I need to manipulate this data to show total ducks sold per sales person and total money earned per salesperson.

Sold Buy       Total Ducks Sold     Total Money Earned
Salesman 1     27                   $110
Salesman 2     6                    $25
Salesman 3     1                    $5

[strongpot]

Here is my feeble code :)

<?php
mysql_select_db($xxx_xxx, $xxx);
$query_rcdReport = "SELECT *,
    GROUP_CONCAT(DISTINCT firstname,lastname)
    FROM ducks
    GROUP BY firstname,lastname ORDER BY soldby";
$rcdReport = mysql_query($query_rcdReport, $bgrotary) or die(mysql_error());
$row_rcdReport = mysql_fetch_assoc($rcdReport);
$totalRows_rcdReport = mysql_num_rows($rcdReport);
?>