bjstyl2 Posted August 18, 2014 Share Posted August 18, 2014 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 More sharing options...
niche Posted August 18, 2014 Share Posted August 18, 2014 You probably need to use DISTINCT http://www.w3schools.com/sql/sql_distinct.asp Link to comment Share on other sites More sharing options...
davej Posted August 19, 2014 Share Posted August 19, 2014 See... http://www.w3schools.com/sql/sql_groupby.asp Link to comment Share on other sites More sharing options...
bjstyl2 Posted August 19, 2014 Author Share Posted August 19, 2014 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 More sharing options...
justsomeguy Posted August 19, 2014 Share Posted August 19, 2014 You didn't show the query you're trying to use, but you need to use a join. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now