goku0650 goku0650 - 3 months ago 7
SQL Question

Creating a script to sum 1 column within multiple tables

I am trying create 1 script in which will result in a total sum of a column within about 30 tables. Right now, I have the following script that does work but not the way I want.

Select CustomerID, sum(convert(numeric(18,1),BilledDuration/60.0)) AS Minutes
From Table 1
Where RetailRate = 0
AND Direction = 'O'
Group By CustomerID
UNION
Select CustomerID, sum(convert(numeric(18,1),BilledDuration/60.0)) AS Minutes
From Table 2
Where RetailRate = 0
AND Direction = 'O'
Group By CustomerID

Order By CustomerID


So the results end up giving me the customer twice but the tables are for each day of the month and so that could be about 30 rows per customer or so. So I am wanting to combine those rows into 1 with the total sum of the minutes of all the tables. Any kind of help would be much appreciated. Thanks!

Answer

Do the UNION ALL in a derived table. GROUP BY the result.

Select CustomerID, sum(convert(numeric(18,1),BilledDuration/60.0)) AS Minutes
from
(
select CustomerID, BilledDuration
From Table 1
Where RetailRate = 0
  AND Direction = 'O'
UNION ALL
Select CustomerID, BilledDuration
From Table 2
Where RetailRate = 0
  AND Direction = 'O'
)
Group By CustomerID

Order By CustomerID

Note: Why do you have so similar tables? Perhaps you should store all data in the same table.