goku0650 - 10 months ago 34

SQL Question

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.

Source (Stackoverflow)