Solved

Combine 2 Temporary Tables SQL

Posted on 2008-06-18
7
917 Views
Last Modified: 2012-06-27
I have 2 sets of temporary tables I am using to pull some data and sum some fields.  

Right now my results look like this:

Count1 Count2 Code Name
   0           1         XR   Center1
   0           4         NT   Center2
   3           0         XR   Center1
   4           0         NT   Center2

I want it to look like this:

Count1 Count2 Code Name
   3          1         XR   Center1
   4          4         NT   Center2
   

I have included a copy of my stored procedure.
sproc1
0
Comment
Question by:Christopher Gore
  • 3
  • 2
  • 2
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21815631
what about this:
select sum(count1), sum(count2), code, name
from yourtable 
group by code, name

Open in new window

0
 
LVL 1

Author Comment

by:Christopher Gore
ID: 21815694
Produces the same results.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21815748
what I meant is this:
SELECT SUM(CanAppts) CanAppts

     , SUM(ALLAppts) ALLAppts

     , ModalityCode

     , ListName 

FROM (

SELECT DISTINCT SUM(CPTCode) AS CanAppts, SUM(CPTCode2) AS ALLAppts, ModalityCode, ListName from @CT

GROUP BY ListName, ModalityCode

UNION ALL

SELECT DISTINCT SUM(CPTCode) AS CanAppts, SUM(CPTCode2) AS ALLAppts, ModalityCode, ListName from @MR

GROUP BY ListName, ModalityCode

UNION ALL

SELECT DISTINCT SUM(CPTCode) AS CanAppts, SUM(CPTCode2) AS ALLAppts, ModalityCode, ListName from @US

GROUP BY ListName, ModalityCode

UNION ALL

SELECT DISTINCT SUM(CPTCode) AS CanAppts, SUM(CPTCode2) AS ALLAppts, ModalityCode, ListName from @FL

GROUP BY ListName, ModalityCode

UNION ALL

SELECT DISTINCT SUM(CPTCode) AS CanAppts, SUM(CPTCode2) AS ALLAppts, ModalityCode, ListName from @NM

GROUP BY ListName, ModalityCode

UNION ALL

SELECT DISTINCT SUM(CPTCode) AS CanAppts, SUM(CPTCode2) AS ALLAppts, ModalityCode, ListName from @AR

GROUP BY ListName, ModalityCode

UNION ALL

SELECT DISTINCT SUM(CPTCode) AS CanAppts, SUM(CPTCode2) AS ALLAppts, ModalityCode, ListName from @CT2

GROUP BY ListName, ModalityCode

UNION ALL

SELECT DISTINCT SUM(CPTCode) AS CanAppts, SUM(CPTCode2) AS ALLAppts, ModalityCode, ListName from @MR2

GROUP BY ListName, ModalityCode

UNION ALL

SELECT DISTINCT SUM(CPTCode) AS CanAppts, SUM(CPTCode2) AS ALLAppts, ModalityCode, ListName from @US2

GROUP BY  ListName, ModalityCode

UNION ALL

SELECT DISTINCT SUM(CPTCode) AS CanAppts, SUM(CPTCode2) AS ALLAppts, ModalityCode, ListName from @FL2

GROUP BY ListName, ModalityCode

UNION ALL

SELECT DISTINCT SUM(CPTCode) AS CanAppts, SUM(CPTCode2) AS ALLAppts, ModalityCode, ListName from @NM2

GROUP BY ListName, ModalityCode

UNION ALL

SELECT DISTINCT SUM(CPTCode) AS CanAppts, SUM(CPTCode2) AS ALLAppts, ModalityCode, ListName from @AR2

GROUP BY ListName, ModalityCode

) subquery 

GROUP BY ModalityCode

     , ListName 

ORDER BY SUM(CanAppts) 

Open in new window

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 2

Accepted Solution

by:
climbingjaffa earned 500 total points
ID: 21815792
angelIII suggestion is correct see the example below ...


Ps. i ain't looking points of this one i was just intrigued since i thought the code above should work

 

create table test (

		Count1 int,

		Count2 int,

		Code char(2),

		Name char(10)

		)
 

	Insert into test

	Select  0,           1,         'XR',   'Center1'

	Insert into test

	Select   0,           4,         'NT',   'Center2'

	Insert into test

	Select   3,           0,         'XR' ,  'Center1'

	Insert into test

	Select   4,           0,         'NT' ,  'Center2'
 

select sum(count1), sum(count2), code, name

from test 

group by code, name

Open in new window

0
 
LVL 1

Author Comment

by:Christopher Gore
ID: 21816369
That Worked great the only thing I have to add is I did not want to create a permanent table, so you can alter above to create a temp table in your sproc like so:

Declare @test TABLE (
                Count1 int,
                Count2 int,
                Code char(2),
                Name char(10)
                )
 
        Insert into @test
        Select  0,           1,         'XR',   'Center1'
        Insert into @test
        Select   0,           4,         'NT',   'Center2'
        Insert into @test
        Select   3,           0,         'XR' ,  'Center1'
        Insert into @test
        Select   4,           0,         'NT' ,  'Center2'
 
select sum(count1), sum(count2), code, name
from @test
group by code, name
               
0
 
LVL 1

Author Closing Comment

by:Christopher Gore
ID: 31468490
Great job.  Easy to understand.
0
 
LVL 2

Expert Comment

by:climbingjaffa
ID: 21855268
while i appreciate the points ...really they should go to angelIII his answer was correct and was in before mine so he deserves the points the only difference is that he uses a derived table as opposed to a perm table or table variable
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now