Author |
Topic |
bjstyl2
Starting Member
12 Posts |
Posted - 2014-08-18 : 13:33:50
|
I am trying to use the inner join function and group by to provide a sum of information for a range of dates. Currently i am provided with each date as separate records using the script below. quote: select salesmn.store, salesmn.salesman as employee_num, salesmn.sales, salesmn.spiffs, employee.first, employee.last from employee inner join salesmn on (employee.login = salesmn.salesman) where salesmn.dte between '1/1/2014' and '1/3/2014' order by salesmn.store, employee.last asc
I want to use this code or something similar to provide a sum of the spiffs and sales for the customer. quote: Select salesman, sum(sales) as TotalSales, sum(spiffs) as Total_Spiffs from salesmn group by salesman
Any help is appreciated. Please let me know if I can provide more information. |
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-18 : 13:40:03
|
Where do the columns 'sales' and 'spiffs' come from? |
|
|
bjstyl2
Starting Member
12 Posts |
Posted - 2014-08-18 : 14:00:37
|
salesmn.sales, salesmn.spiffsThey come from the salesmn table. |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-18 : 14:17:51
|
OKSELECT store, sum(sales) as TotalSales, sum(spiffs) as Total_SpiffsFrom Salesmngroup by storeorder by store |
|
|
bjstyl2
Starting Member
12 Posts |
Posted - 2014-08-18 : 15:05:34
|
Is there a way to combine this with the first query? I am also looking to group by salesman not by store. Thanks for all your help. I just cant figure it out. |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-18 : 15:11:00
|
You can combine it with the first query but why? You're not using anything from the employee table in your new query.Go ahead and change the grouping to what you need. |
|
|
bjstyl2
Starting Member
12 Posts |
Posted - 2014-08-18 : 15:40:16
|
I need to retrieve the employee's first and last name from the employee table to make it easier to payout the spiffs. |
|
|
bjstyl2
Starting Member
12 Posts |
Posted - 2014-08-18 : 16:50:03
|
So here is the structure of the database...Table Employee:- Login -> (Salesperson #)- First- LastTable Salesmn- DTE -> (Daily Date)- Store- Salesman -> (Salesperson #)- Sales- SpiffsWhat I would like to get out of this is....Store - Salesperson # - Sum of Sales - Sum of Spiffs - First Name - Last Namefor a given period 1/1/2014 to 1/2/2014. Currently my method: quote: select salesmn.store, salesmn.salesman as employee_num, salesmn.sales, salesmn.spiffs, employee.first, employee.last from employee inner join salesmn on (employee.login = salesmn.salesman) where salesmn.dte between '1/1/2014' and '1/3/2014' order by salesmn.store, employee.last asc
provides me with the information as separate dates. I would like for them to be combined so that I could have 1 record per person with the sum of sales and spiffs. Thanks again for any assistance. |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-19 : 07:56:35
|
OK -- this is getting a little complicated. Would you please provide:1. CREATE TABLE statements to create the tables used in this query2. INSERT INTO statements to populate the tables with test date3. The desired result of the query you are looking for when executed on the test tables |
|
|
bjstyl2
Starting Member
12 Posts |
Posted - 2014-08-19 : 15:13:18
|
Here is my information..../* Create a table called employee */CREATE TABLE employee(Id integer PRIMARY KEY, login text, first text, last text);/* Create few records in this table */INSERT INTO employee VALUES(1,'12345','Bob','Smith');INSERT INTO employee VALUES(2,'54321','John','Williams');INSERT INTO employee VALUES(3,'ABCDE','Jane','Doe');INSERT INTO employee VALUES(4,'EDCBA','Mike','Jones');/* Create a table called salesmn */CREATE TABLE salesmn(Id integer PRIMARY KEY, dte date, salesman text, store int, sales decimal, spiffs decimal);/* Create few records in this table */INSERT INTO salesmn VALUES(1,'2014-01-01','12345','1','100.00','5.00');INSERT INTO salesmn VALUES(2,'2014-01-01','54321','2','10.00','5.00');INSERT INTO salesmn VALUES(3,'2014-01-01','ABCDE','1','50.00','2.00');INSERT INTO salesmn VALUES(4,'2014-01-02','12345','1','100.00','1.00');INSERT INTO salesmn VALUES(5,'2014-01-02','EDCBA','1','200.00','5.00');INSERT INTO salesmn VALUES(6,'2014-01-02','54321','1','30.00','0.00');
Result that I would like to see is....Employee #, First Name, Last Name, Sum of Sales for date range, Sum of Spiffs for date range12345, Bob, Smith, 200.00, 6.0054321, John, Williams, 40.00, 5.00ABCDE, Jane, Doe, 50.00, 2.00EDCBA, Mike, Jones, 200.00, 5.00Thanks again for your help! I just can't figure this part out. I know I can take my current information and create a pivot table, but I was hoping to limit the touches to the information so that multiple people can run this report easily. |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-08-20 : 08:09:04
|
Here's one way. Note that I changed the datatype 'text' in your post to 'varchar(50)'. Try to avoid datatype text. If you must have very large columns, use varchar(max) instead. However, in your case I cannot imagine that any of those columns would be very large./* Create a table called employee */drop TABLE employeeCREATE TABLE employee(Id integer PRIMARY KEY, login varchar(50), first varchar(50), last varchar(50));/* Create few records in this table */INSERT INTO employee VALUES(1,'12345','Bob','Smith');INSERT INTO employee VALUES(2,'54321','John','Williams');INSERT INTO employee VALUES(3,'ABCDE','Jane','Doe');INSERT INTO employee VALUES(4,'EDCBA','Mike','Jones');/* Create a table called salesmn */drop TABLE salesmnCREATE TABLE salesmn(Id integer PRIMARY KEY, dte date, salesman varchar(50), store int, sales decimal, spiffs decimal);/* Create few records in this table */INSERT INTO salesmn VALUES(1,'2014-01-01','12345','1','100.00','5.00');INSERT INTO salesmn VALUES(2,'2014-01-01','54321','2','10.00','5.00');INSERT INTO salesmn VALUES(3,'2014-01-01','ABCDE','1','50.00','2.00');INSERT INTO salesmn VALUES(4,'2014-01-02','12345','1','100.00','1.00');INSERT INTO salesmn VALUES(5,'2014-01-02','EDCBA','1','200.00','5.00');INSERT INTO salesmn VALUES(6,'2014-01-02','54321','1','30.00','0.00');/* Produce desired output */SELECT s.id ,e.first ,e.last ,sum(sales) ,sum(spiffs)FROM salesmn sJOIN employee e ON s.Id = e.IdWHERE s.dte >= '2014-01-01' AND s.dte < '2014-01-03'GROUP BY s.id ,e.first ,e.lastORDER BY s.id |
|
|
|
|
|