Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query -- Inner Join and Group by (using sum)

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?
Go to Top of Page

bjstyl2
Starting Member

12 Posts

Posted - 2014-08-18 : 14:00:37
salesmn.sales, salesmn.spiffs

They come from the salesmn table.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-18 : 14:17:51
OK


SELECT store, sum(sales) as TotalSales, sum(spiffs) as Total_Spiffs
From Salesmn
group by store
order by store
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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
- Last

Table Salesmn
- DTE -> (Daily Date)
- Store
- Salesman -> (Salesperson #)
- Sales
- Spiffs

What I would like to get out of this is....

Store - Salesperson # - Sum of Sales - Sum of Spiffs - First Name - Last Name
for 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.
Go to Top of Page

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 query
2. INSERT INTO statements to populate the tables with test date
3. The desired result of the query you are looking for when executed on the test tables
Go to Top of Page

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 range
12345, Bob, Smith, 200.00, 6.00
54321, John, Williams, 40.00, 5.00
ABCDE, Jane, Doe, 50.00, 2.00
EDCBA, Mike, Jones, 200.00, 5.00

Thanks 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.
Go to Top of Page

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 employee
CREATE 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 salesmn
CREATE 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 s
JOIN employee e ON s.Id = e.Id
WHERE s.dte >= '2014-01-01'
AND s.dte < '2014-01-03'
GROUP BY s.id
,e.first
,e.last
ORDER BY s.id


Go to Top of Page
   

- Advertisement -