Solved

Combine 2 Temporary Tables SQL

Posted on 2008-06-18
7
927 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 143

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 143

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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