Prev: Distinct issues
Next: Column not getting updated
From: Luigi on 21 Apr 2010 06:31
Hi all
I have 4 tables containing the fields Month (int) and Value (decimal
(28,15)), plus various other descriptive fields, and for each month can be
present multiple records.
Here an example:

Create table TableA (month int, decimal value (28,15))

Select * from TableA

-----
1-3.46
2-4.88
2-3,2344
3-12.0
4-3.5
....... (as you can see for February there are more than a record)

How can I, from these 4 tables (they always have "month" and "Value" as
fields important to me, then change the other columns descriptive), fills
another table that has 12 record (one per month) and as the value of the sum
(there may be null) the sum of all the numeric values of the respective
months caught by 4 base tables?
I'm can using only SQL Server 2000.

Thanks in advance to those who can give me a hand.

Luigi

From: Philipp Post on 21 Apr 2010 07:33
INSERT INTO new_table (month_nbr, some_value)
SELECT month_nbr, SUM(some_value)
FROM
(SELECT month_nbr, some_value
FROM Tbl1
UNION ALL
SELECT month_nbr, some_value
FROM Tbl2
UNION ALL
SELECT month_nbr, some_value
FROM Tbl3
UNION ALL
SELECT month_nbr, some_value
FROM Tbl4
) AS X
GROUP BY month_nbr;

brgds

Philipp Post

From: Uri Dimant on 21 Apr 2010 07:44
Luigi
INSERT INTO tbl SELECT month,SUM(value) FROM tbl GROUP BY month



"Luigi" <Luigi(a)discussions.microsoft.com> wrote in message
news:EC93D2EB-128A-4286-8B5A-C16A3B55548D(a)microsoft.com...
> Hi all
> I have 4 tables containing the fields Month (int) and Value (decimal
> (28,15)), plus various other descriptive fields, and for each month can be
> present multiple records.
> Here an example:
>
> Create table TableA (month int, decimal value (28,15))
>
> Select * from TableA
>
> -----
> 1-3.46
> 2-4.88
> 2-3,2344
> 3-12.0
> 4-3.5
> ...... (as you can see for February there are more than a record)
>
> How can I, from these 4 tables (they always have "month" and "Value" as
> fields important to me, then change the other columns descriptive), fills
> another table that has 12 record (one per month) and as the value of the
> sum
> (there may be null) the sum of all the numeric values of the respective
> months caught by 4 base tables?
> I'm can using only SQL Server 2000.
>
> Thanks in advance to those who can give me a hand.
>
> Luigi
>


From: Luigi on 21 Apr 2010 08:34
"Philipp Post" wrote:

> INSERT INTO new_table (month_nbr, some_value)
> SELECT month_nbr, SUM(some_value)
> FROM
> (SELECT month_nbr, some_value
> FROM Tbl1
> UNION ALL
> SELECT month_nbr, some_value
> FROM Tbl2
> UNION ALL
> SELECT month_nbr, some_value
> FROM Tbl3
> UNION ALL
> SELECT month_nbr, some_value
> FROM Tbl4
> ) AS X
> GROUP BY month_nbr;

Thank you Philipp.

Luigi
 | 
Pages: 1
Prev: Distinct issues
Next: Column not getting updated