CODEWITHSUNDEEP
sql-server-2008
user1296762
user1296762

Reputation: 512

using a count from one table in another table

So i have genereated a number from a count in #table1 which I want to show in another table that I have created. So the syntax for the first count is 

select COUNT(*)
into #table1
from #test8
where account_number1 is null
GROUP BY varmonth,MONYEAR
ORDER BY varmonth, MONYEAR

Then the second table is 

SELECT MONYEAR, 
COUNT(*) AS TOTAL,
SUM(CURRENT_BALANCE_AMOUNT) REH_BAL,
FROM #table1
WHERE ROWNUMBER = 1 
GROUP BY varmonth,MONYEAR
ORDER BY varmonth, MONYEAR

However I want that first count to slide in between the count and sum in the second table. is there a way of doing this so all the numbers output in one final table? Thanks

Upvotes: 2

Views: 106

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You can do this:

SELECT
  t2.MONYEAR,
  t2.Total,
  t1.tcount,
  t2.REH_BAL
FROM
(
  select varmonth, MONYEAR, COUNT(*) tcount
  from #test8
  where account_number1 is null 
  GROUP BY varmonth,MONYEAR
) t1
INNER JOIN
(
  SELECT MONYEAR, 
    COUNT(*) AS TOTAL,
    SUM(CURRENT_BALANCE_AMOUNT) REH_BAL,
  FROM #table1
  WHERE ROWNUMBER = 1 
  GROUP BY varmonth,MONYEAR
) t2 ON t1.MONYEAR = t2.MONYEAR
ORDER BY t1.varmontth, t2.MONYEAR

Upvotes: 1

Related Questions