March 3, 2012 at 11:19 pm
Hello
I have two tables for rxample, t1 and t2.
both tables are having same columns name (monthno, amount)
i want to sum up amount group by monthno.( some time a month no might be available in either table only).
Regards
March 4, 2012 at 12:24 am
Best use a 'calendar table'; A table that has a row for each period that you want to group by on. Such tables are very handy in many queries, so many people have one materialized in their db at all times. If you do not have one, you can -at some performance cost- create a temporary one using for example a common table expression (cte).
I assume you don't have one handy, so I'll give an example that uses a cte to generate a small calendar table by generating a numbers list first. But I suggest you search for both 'Tally table'(or 'numbers table') and 'calendar table'. More info on tally tables can be found via the link in my footer text, for calendar tables you can try one of these articles.
This example generates a numbers list in cteNumbers. Then it uses union all to combine all rows of the both tables t1 and t2. The combined results are then grouped by the monthno value per monthly period by checking if their monthno is >= the start of the monthly period and less than the start of the next period. And then it groups the rows together on the n-values. Finally, there is a where clause that says not to collect for more than 36 months. As you will find out, this is so fast that without the where, in no time you'll get an 'out of range' - error when you've reached december 1st, 9999. i.e. either limit the range your numbers cte generates or put a where in to limit the range.
with cteNumbers as (
select row_number() over (order by (select null)) as n
from sys.syscolumns sc1, sys.syscolumns sc2
)
select dateadd( month, n.n - 1, {d '2010-01-01'}) as [month], sum(t.amount) as amount
from cteNumbers n
left outer join (
select monthno, amount
from t1
union all
select monthno, amount
from t2
) t on (t.monthno >= dateadd( month, n.n - 1, {d '2010-01-01'}) and t.monthno < dateadd( month, n.n, {d '2010-01-01'}))
where n.n < 36
group by n.n
Do NOT put a where dateadd(month, n.n, {d '2010-01-01'}) <= somedate instead of the where n.n < 36. You'll see why if you try.
March 6, 2012 at 3:28 am
This uses CTE and UNION
CREATE TABLE t1(
[MonthNo] [int],
[Amount] [money]
) ON [PRIMARY]
CREATE TABLE t2(
[MonthNo] [int],
[Amount] [money]
) ON [PRIMARY]
Insert into t1 values (1,111)
Insert into t1 values (2,211)
Insert into t1 values (3,311)
Insert into t2 values (1,11)
Insert into t2 values (2,21)
Insert into t2 values (3,31);
With AmagamatedCTE(MonthNo,Amount) as
(select MonthNo, Amount from t1
Union All
select MonthNo, Amount from t1)
Select MonthNo,Sum(amount) as amount from AmagamatedCTE
group By MonthNo
drop table t1
drop table t2
March 6, 2012 at 3:29 am
Sorry one typo
CREATE TABLE t1(
[MonthNo] [int],
[Amount] [money]
) ON [PRIMARY]
CREATE TABLE t2(
[MonthNo] [int],
[Amount] [money]
) ON [PRIMARY]
Insert into t1 values (1,111)
Insert into t1 values (2,211)
Insert into t1 values (3,311)
Insert into t2 values (1,11)
Insert into t2 values (2,21)
Insert into t2 values (3,31);
With AmagamatedCTE(MonthNo,Amount) as
(select MonthNo, Amount from t1
Union All
select MonthNo, Amount from t2)
Select MonthNo,Sum(amount) as amount from AmagamatedCTE
group By MonthNo
drop table t1
drop table t2
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply