Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 947
  • Last Modified:

Combine 2 Temporary Tables SQL

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
Christopher Gore
Asked:
Christopher Gore
  • 3
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
select sum(count1), sum(count2), code, name
from yourtable 
group by code, name

Open in new window

0
 
Christopher GoreSolutions ArchitectAuthor Commented:
Produces the same results.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
climbingjaffaCommented:
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
 
Christopher GoreSolutions ArchitectAuthor Commented:
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
 
Christopher GoreSolutions ArchitectAuthor Commented:
Great job.  Easy to understand.
0
 
climbingjaffaCommented:
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

Independent Software Vendors: 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!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now