SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query -- Inner Join and Group by (using sum)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bjstyl2
Starting Member

8 Posts

Posted - 08/18/2014 :  13:33:50  Show Profile  Reply with Quote
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
Aged Yak Warrior

850 Posts

Posted - 08/18/2014 :  13:40:03  Show Profile  Reply with Quote
Where do the columns 'sales' and 'spiffs' come from?
Go to Top of Page

bjstyl2
Starting Member

8 Posts

Posted - 08/18/2014 :  14:00:37  Show Profile  Reply with Quote
salesmn.sales, salesmn.spiffs

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

gbritton
Aged Yak Warrior

850 Posts

Posted - 08/18/2014 :  14:17:51  Show Profile  Reply with Quote
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

8 Posts

Posted - 08/18/2014 :  15:05:34  Show Profile  Reply with Quote
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
Aged Yak Warrior

850 Posts

Posted - 08/18/2014 :  15:11:00  Show Profile  Reply with Quote
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

8 Posts

Posted - 08/18/2014 :  15:40:16  Show Profile  Reply with Quote
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

8 Posts

Posted - 08/18/2014 :  16:50:03  Show Profile  Reply with Quote
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
Aged Yak Warrior

850 Posts

Posted - 08/19/2014 :  07:56:35  Show Profile  Reply with Quote
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

8 Posts

Posted - 08/19/2014 :  15:13:18  Show Profile  Reply with Quote
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.

Edited by - bjstyl2 on 08/19/2014 15:15:09
Go to Top of Page

gbritton
Aged Yak Warrior

850 Posts

Posted - 08/20/2014 :  08:09:04  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000