0

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.

Edited by strongpot

3
Contributors
8
Replies
67
Views
4 Years
Discussion Span
Last Post by strongpot
0

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.

Edited by pritaeas

0

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

0

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?

0

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       dickjones@isp.com    25          
2   Salesman 1   Dick         Jones       dickjones@isp.com    25          
3   Salesman 1   Dick         Jones       dickjones@isp.com    25          
4   Salesman 1   Dick         Jones       dickjones@isp.com    25          
5   Salesman 1   Dick         Jones       dickjones@isp.com    25         
6   Salesman 1   Dick         Jones       dickjones@isp.com    25         
7   Salesman 1   Dick         Jones       dickjones@isp.com    25        
8   Salesman 1   Dick         Jones       dickjones@isp.com    25          
9   Salesman 1   Dick         Jones       dickjones@isp.com    25          
10  Salesman 1   Dick         Jones       dickjones@isp.com    25           
11  Salesman 1   Dick         Jones       dickjones@isp.com    25           
12  Salesman 1   Dick         Jones       dickjones@isp.com    25          
13  Salesman 1   Dick         Jones       dickjones@isp.com    25          
14  Salesman 1   Dick         Jones       dickjones@isp.com    25          
15  Salesman 1   Dick         Jones       dickjones@isp.com    25         
16  Salesman 1   Dick         Jones       dickjones@isp.com    25         
17  Salesman 1   Dick         Jones       dickjones@isp.com    25         
18  Salesman 1   Dick         Jones       dickjones@isp.com    25          
19  Salesman 1   Dick         Jones       dickjones@isp.com    25          
20  Salesman 1   Dick         Jones       dickjones@isp.com    25           
21  Salesman 1   Dick         Jones       dickjones@isp.com    25          
22  Salesman 1   Dick         Jones       dickjones@isp.com    25           
23  Salesman 1   Dick         Jones       dickjones@isp.com    25          
24  Salesman 1   Dick         Jones       dickjones@isp.com    25          
25  Salesman 1   D            Jones       dickjones@isp.com    25           
26  Salesman 2   Clarence     Boddicker   clbod@isp.com        6  
27  Salesman 2   Clarence     Boddicker   clbod@isp.com        6
28  Salesman 2   Clarence     Boddicker   clbod@isp.com        6
29  Salesman 2   Clarence     Boddicker   clbod@isp.com        6
30  Salesman 2   Clarence     Boddicker   clbod@isp.com        6
31  Salesman 2   Clarence     Boddicker   clbod@isp.com        6 
32  Salesman 3   Anne         Lewis       alewis@isp.com       1 
33  Salesman 1   Alex         Murphy      murphy@isp.com       1
34  Salesman 1   Fred         Friendly    friendly@isp.com     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
0

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);
?>

Edited by strongpot

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.