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
 SUM 1 column and select multiple columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pats83
Starting Member

4 Posts

Posted - 07/01/2013 :  19:33:44  Show Profile  Reply with Quote
Hi I am trying to grab multiple columns from multiple tables and SUM one column where the field name MPRO is the same.
I have 2 SQL queries that work when separate and I need them to be combined to populate the correct information.

SQL1 = "SELECT MPRO.MPRO, SUM(MPRO.TimeTook) AS TimeTook, MPRO.Carrier, MPRO.Product, MPRO.Province, MPRO.Record, MASTER.DateIn, JOBS.JobTypeCode "
        SQL2 = "FROM Worklog.Worklog.MPRO MPRO INNER JOIN WorkLog.WorkLog.MASTER "
        SQL3 = "MASTER ON MPRO.Record=MASTER.Record JOIN WorkLog.WorkLog.JOBS "
        SQL4 = "JOBS ON MPRO.MPro=JOBS.MPro WHERE (MASTER.DateIn>={ts '" + Str(StartYear) + "-" + StartMonthStr + "-" + StartDayStr + " 00:00:00'} "
        SQL5 = "AND MASTER.DateIn<={ts '" + Str(EndYear) + "-" + EndMonthStr + "-" + EndDayStr + " 00:00:01'}) ) "
        SQL7 = "GROUP BY MPRO.MPRO, MPRO.Carrier, MPRO.Product, MPRO.Province, MPRO.Record, MASTER.DateIn, JOBS.JobTypeCode"

The above are the all fields I need
Below is the field I need to SUM and it needs to be GROUP BY the MPRO field.


        SQLStatement = "SELECT MPRO, SUM(TimeTook) AS TimeTook FROM MPRO GROUP BY MPRO"
        SQLStatement = SQL1 + SQL2 + SQL3 + SQL4 + SQL5 + SQL7

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/01/2013 :  19:48:36  Show Profile  Reply with Quote
Can you show us with some example data, how you want the output to look like?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/02/2013 :  01:59:59  Show Profile  Reply with Quote
sounds like this to me


SQL1 = "SELECT MPRO.MPRO, SUM(MPRO.TimeTook) OVER (PARTITION BY MPRO.MPRO) AS TimeTook, MPRO.Carrier, MPRO.Product, MPRO.Province, MPRO.Record, MASTER.DateIn, JOBS.JobTypeCode "
        SQL2 = "FROM Worklog.Worklog.MPRO MPRO INNER JOIN WorkLog.WorkLog.MASTER "
        SQL3 = "MASTER ON MPRO.Record=MASTER.Record JOIN WorkLog.WorkLog.JOBS "
        SQL4 = "JOBS ON MPRO.MPro=JOBS.MPro WHERE (MASTER.DateIn>={ts '" + Str(StartYear) + "-" + StartMonthStr + "-" + StartDayStr + " 00:00:00'} "
        SQL5 = "AND MASTER.DateIn<={ts '" + Str(EndYear) + "-" + EndMonthStr + "-" + EndDayStr + " 00:00:01'}) ) "


I hope you're on SQL 2005 or above version

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Pats83
Starting Member

4 Posts

Posted - 07/02/2013 :  09:44:22  Show Profile  Reply with Quote
Hi I tried with the OVER (PARTITION) and it did not work. I am using VB in Excel to create a small program.
MPRO table
MPRO | TimeTook | Carrier | Product | Province | Record
54 | 30 | GA | auto | ON | 1
73 | 15 | BR | auto | ON | 2
54 | 40 | GA | auto | ON | 3

JOBS table
MPRO | JobTypeCode
54 | A
73 | B

What I would like to have is:
MPRO | TimeTook | Carrier | Product | Province | JobTypeCode
54 | 70 | GA | auto | ON | A
73 | 15 | BR | auto | ON | B

Where the TimeTook is the total of the same MPRO#
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/02/2013 :  09:45:56  Show Profile  Reply with Quote
quote:
Originally posted by Pats83

Hi I tried with the OVER (PARTITION) and it did not work. I am using VB in Excel to create a small program.
MPRO table
MPRO | TimeTook | Carrier | Product | Province | Record
54 | 30 | GA | auto | ON | 1
73 | 15 | BR | auto | ON | 2
54 | 40 | GA | auto | ON | 3

JOBS table
MPRO | JobTypeCode
54 | A
73 | B

What I would like to have is:
MPRO | TimeTook | Carrier | Product | Province | JobTypeCode
54 | 70 | GA | auto | ON | A
73 | 15 | BR | auto | ON | B

Where the TimeTook is the total of the same MPRO#


so are you looking at a VBA code? the one i gave was t-sql code and it will work well in SQL Server so far as version is over 2005

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Pats83
Starting Member

4 Posts

Posted - 07/02/2013 :  10:20:02  Show Profile  Reply with Quote
Yes it is VBA code.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/02/2013 :  14:55:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- Prepare proper sample data
DECLARE	@mPro TABLE
	(
		mPro TINYINT NOT NULL,
		TimeTook TINYINT NOT NULL,
		Carrier CHAR(2) NOT NULL,
		Product CHAR(4) NOT NULL,
		Province CHAR(2) NOT NULL,
		Record TINYINT NOT NULL
	);

INSERT	@mPro
VALUES	(54, 30, 'GA', 'auto', 'ON', 1),
	(73, 15, 'BR', 'auto', 'ON', 2),
	(54, 40, 'GA', 'auto', 'ON', 3);

DECLARE	@Jobs TABLE
	(
		mPro TINYINT NOT NULL,
		JobTypeCode CHAR(1) NOT NULL
	);

INSERT	@Jobs
VALUES	(54, 'A'),
	(73, 'B');

-- Solution by SwePeso
SELECT		m.mPro,
		SUM(m.TimeTook) AS TimeTook,
		m.Carrier,
		m.Product,
		m.Province,
		j.JobTypeCode
FROM		@mPro AS m
INNER JOIN	@Jobs AS j ON j.mPro = m.mPro
GROUP BY	m.mPro,
		m.Carrier,
		m.Product,
		m.Province,
		j.JobTypeCode;



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Pats83
Starting Member

4 Posts

Posted - 07/02/2013 :  15:27:49  Show Profile  Reply with Quote
Thanks SwePeso!
That worked.
THanks :)
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.08 seconds. Powered By: Snitz Forums 2000