Jump to content

HELP -> Joining 2 tables, grouping by a common value in both tables


bjstyl2

Recommended Posts

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:

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

My current solution 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 for any assistance.

Link to comment
Share on other sites

Here is how my database is setup:
/* 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.00

I can get the sum of Sales and Spiffs without the employee name, but every time I try to include the employee name I get an error that says invalid column reference.

I bet it is something small that I am missing, just can't wrap my head around it yet.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...