strongpot Posted August 20, 2013 Share Posted August 20, 2013 (edited) 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 August 20, 2013 by Zane Quote Link to comment https://forums.phpfreaks.com/topic/281400-grouping-data-and-getting-sums/ Share on other sites More sharing options...
Muddy_Funster Posted August 20, 2013 Share Posted August 20, 2013 That's the best integration of RoboCop into a programming question I have ever seen 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. Quote Link to comment https://forums.phpfreaks.com/topic/281400-grouping-data-and-getting-sums/#findComment-1446019 Share on other sites More sharing options...
Zane Posted August 20, 2013 Share Posted August 20, 2013 (edited) I don't know about getting the money earned, but to get the sum, you just GROUP BY soldby and make sure you are using an aggregate function like SUM(*) in your SELECT statement Edited August 20, 2013 by Zane Quote Link to comment https://forums.phpfreaks.com/topic/281400-grouping-data-and-getting-sums/#findComment-1446039 Share on other sites More sharing options...
AbraCadaver Posted August 20, 2013 Share Posted August 20, 2013 That's the best integration of RoboCop into a programming question I have ever seen I don't get it. I'ts been well over 20 years since I've seen it, and it wasn't that great, but is it the peoples names? I thought they were all just called citizen. Quote Link to comment https://forums.phpfreaks.com/topic/281400-grouping-data-and-getting-sums/#findComment-1446045 Share on other sites More sharing options...
Barand Posted August 21, 2013 Share Posted August 21, 2013 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 Quote Link to comment https://forums.phpfreaks.com/topic/281400-grouping-data-and-getting-sums/#findComment-1446047 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.