Question: I read some topics about this but I'm not very good with sql. I have 10 tables with these fields:

* value
* type
* date

I want to sum all the value fileds together when they have a specific type. I was trying to do something like this, but it's not working.

```
select sum(tab1.value) + sum(tab2.value)
from tab1, tab2
where tab1.type = tab2.type = 'box'

```

I guess I could do many simple queries like these and then sum all the results

```
select sum(value) from tab1 where type='box'
select sum(value) from tab2 where type='box'

```

but I wonder if I can do one single query

thanks

Answer: 
Having multiple tables with the same structure is usually a sign of poor database design.

I would suggest that you use your last approach, but put the subqueries in the `from` clause and then add the results in the `select`:

```
select t1.value + t2.value + . . 
from (select sum(value) as value from tab1 where type='box') t1 cross join
     (select sum(value) as value from tab2 where type='box') t2 cross join
     . . .

```

Alternatively, you could `union all` them together in the `from` clause and then take the sum:

```
select sum(value)
from ((select sum(value) as value from tab1 where type='box') union all
      (select sum(value) as value from tab2 where type='box') union all
      . . .
     ) t;

```
1.Use single select;

```
DECLARE @type NVARCHAR(255) = N'Box';

SELECT (SELECT SUM(value) FROM tab1 WHERE type=@Box)
    + (SELECT SUM(value) FROM tab2 WHERE type=@Box)
    + (SELECT SUM(value) FROM tab3 WHERE type=@Box)
    + (...)

```

I think it's simplest one.

2.you create a view as

```
CREATE VIEW tabs
AS
    SELECT value, type FROM tab1
    UNION
    SELECT value, type FROM tab2
    UNION
    SELECT value, type FROM tab3
    UNION
       ...

```

Then 

```
SELECT SUM(value) FROM tabs WHERE type = 'BOX'

```

3.Think why similar column are different tables. Can they be merged into single table?
If answer is No, and you have too many tables, consider concatenate SQL strings and use sp\_executesql to execute it.