SUM 1 column and select multiple columns

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

SUM 1 column and select multiple columns

Author

Topic

Pats83
Starting Member

4 Posts

Posted - 2013-07-01 : 19:33:44

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

[/code]

Thank you

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-01 : 19:48:36

Can you show us with some example data, how you want the output to look like?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 01:59:59

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

Pats83
Starting Member

4 Posts

Posted - 2013-07-02 : 09:44:22

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#

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 09:45:56

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

Pats83
Starting Member

4 Posts

Posted - 2013-07-02 : 10:20:02

Yes it is VBA code.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-02 : 14:55:36

[code]-- 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;[/code]

N 56°04'39.26"
E 12°55'05.63"

Pats83
Starting Member

4 Posts

Posted - 2013-07-02 : 15:27:49

Thanks SwePeso!
That worked.
THanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources