Jump to content

Grouping data and getting sums


strongpot

Recommended Posts

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       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
Edited by Zane
Link to comment
Share on other sites

That's the best integration of RoboCop into a programming question I have ever seen :D

 

This would be done in the SQL, not the PHP.  Although, unless you have a bunch of fields that you are not listing then your table design is all wrong.  With the information that you are presenting there is no need to have a single line per duck purchased, and in most cases you would link the single sales lines of this table to another table containing the duck level detail by building a relationship between the ids in each table.

 

While you can get the results you are looking for with what you have - Is there a really good reason for this layout as it is painfully inefficient.

Link to comment
Share on other sites

As Muddy said, your data is in need of better organization, but the way you have it now you could

SELECT soldby, 
    SUM(
        CASE 
        WHEN package = 25 THEN 100.00
        WHEN package = 6 THEN 25.00
        WHEN package = 1 THEN 5.00
        END
    ) as moneyEarned,
    SUM(package) as ducksSold
FROM (
    SELECT DISTINCT soldby, email, package
    FROM yourTableName
    ) as ducksales
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.