how to combine 2 aggregate together? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to combine 2 aggregate together?

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-27 : 08:58:25

[code]select trx_type,
sum(case when trx_type = 'PDO' and do_date <= '20070101' then quantity end),
sum(case when trx_type = 'PDO' and do_date <= '20071231' then qtt_out end)
from testing
group by trx_type

select *
from (
select row_number() over (partition by trx_type order by do_date desc)wasa,
quantity, qtt_out, total_price, forex_rate, trx_type, do_date
from testing
)a
where wasa = 1 and do_date <= '20091231'
[/code]
hi, how do i combine this 2 together? both of it also group by trx_type

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-27 : 09:14:47

desire output are top row + sum of the trx_type...

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-27 : 09:20:17

Something like this


select top 1 t1.* from
(
select	trx_type,
		sum(case when trx_type = 'PDO' and do_date <= '20070101' then quantity end),
		sum(case when trx_type = 'PDO' and do_date <= '20071231' then qtt_out end)
from	testing
group by trx_type
) as t1
inner join
(
select	*
from	(
		select	row_number() over (partition by trx_type order by do_date desc)wasa,
				quantity, qtt_out, total_price, forex_rate, trx_type, do_date
		from	testing
		)a
where	wasa = 1 and do_date <= '20091231'
) as t2
on t1.trx_type=t2.trx_type
order by wasa desc

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 09:22:51

this ?


select	*
from	
(
    select  row_number() over (partition by trx_type order by do_date desc)wasa,
	    quantity, qtt_out, total_price, forex_rate, trx_type, do_date,
            sum(case when trx_type = 'PDO' and do_date <= '20070101' then quantity end) over() as qty,
 	    sum(case when trx_type = 'PDO' and do_date <= '20071231' then qtt_out end) over() as qtt_out
    from    testing
)a
where	wasa = 1 and do_date <= '20091231'

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-27 : 09:26:52

[code]
CREATE TABLE Testing(quantity int, qtt_out int, total_price int, forex_rate int, trx_type varchar(3), do_date datetime)
INSERT INTO [dbo].[Testing]([quantity], [qtt_out], [total_price], [forex_rate], [trx_type], [do_date])
SELECT 8508, 6738, 4171, 2, N'PDO', '19990625 00:00:00.000' UNION ALL
SELECT 7917, 2860, 9936, 0, N'CCO', '20200521 00:00:00.000' UNION ALL
SELECT 382, 9680, 4103, 4, N'PDO', '20930203 00:00:00.000' UNION ALL
SELECT 7555, 1778, 253, 0, N'PDO', '20100404 00:00:00.000' UNION ALL
SELECT 7193, 801, 2342, 1, N'WHX', '21690309 00:00:00.000' UNION ALL
SELECT 9817, 1016, 8580, 3, N'PDO', '21360510 00:00:00.000' UNION ALL
SELECT 7786, 2148, 437, 4, N'CCO', '21240215 00:00:00.000' UNION ALL
SELECT 4601, 5580, 8493, 0, N'WHX', '19340219 00:00:00.000' UNION ALL
SELECT 314, 4076, 5633, 4, N'PDO', '19320419 00:00:00.000' UNION ALL
SELECT 9864, 1305, 4918, 3, N'CCO', '21541116 00:00:00.000' UNION ALL
SELECT 2044, 1509, 4914, 2, N'CCO', '20041008 00:00:00.000' UNION ALL
SELECT 1348, 9588, 3002, 1, N'PDO', '20420509 00:00:00.000' UNION ALL
SELECT 963, 8963, 5082, 4, N'CCO', '20221111 00:00:00.000' UNION ALL
SELECT 4677, 1343, 6096, 3, N'PDO', '20990408 00:00:00.000' UNION ALL
SELECT 8734, 2602, 546, 2, N'WHX', '21011229 00:00:00.000' UNION ALL
SELECT 5095, 5282, 6277, 2, N'WHX', '20120717 00:00:00.000' UNION ALL
SELECT 9918, 7316, 3366, 2, N'CCO', '19600614 00:00:00.000' UNION ALL
SELECT 1899, 598, 5226, 4, N'PDO', '19150409 00:00:00.000' UNION ALL
SELECT 1248, 7351, 2761, 3, N'CCO', '19190815 00:00:00.000' UNION ALL
SELECT 8127, 6713, 2116, 4, N'PDO', '19561202 00:00:00.000' UNION ALL
SELECT 815, 88, 1526, 3, N'CCO', '19140114 00:00:00.000' UNION ALL
SELECT 1192, 720, 6069, 1, N'CCO', '19471130 00:00:00.000' UNION ALL
SELECT 7268, 3246, 5437, 4, N'PDO', '20531220 00:00:00.000' UNION ALL
SELECT 6281, 1708, 7211, 3, N'CCO', '20990427 00:00:00.000' UNION ALL
SELECT 8297, 9470, 3414, 2, N'PDO', '19600131 00:00:00.000'[/code]

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-27 : 09:34:23

[code]select trx_type,
sum(case when trx_type = 'PDO' and do_date <= '20070101' then quantity end),
sum(case when trx_type = 'PDO' and do_date <= '20071231' then qtt_out end)
from testing
group by trx_type

select *
from (
select row_number() over (partition by trx_type order by do_date desc)wasa,
quantity, qtt_out, total_price, forex_rate, trx_type, do_date
from testing
)a
where wasa = 1 and do_date <= '30001231'[/code]
expected output are [code]
wasa quantity qtt_out total_price forex_rate trx_type do_date SUM sum
--------- ----------- ----------- ----------- -------- --------- -------------------- ----------- -------
1 9864 1305 4918 3 CCO 2154-11-16 00:00:00.000 NULL NULL
1 9817 1016 8580 3 PDO 2136-05-10 00:00:00.000 44490 54476
1 7193 801 2342 1 WHX 2169-03-09 00:00:00.000 NULL NULL
[/code]

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-27 : 09:44:16

sifu u almost there...but coalesce(44490 and 54476, null and null)

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-27 : 10:02:21

anyone? i think sifu slept

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 10:05:35

[code]
select *
from
(
select row_number() over (partition by trx_type order by do_date desc)wasa,
quantity, qtt_out, total_price, forex_rate, trx_type, do_date,
sum(case when trx_type = 'PDO' and do_date <= '20070101' then quantity end) over(partition by trx_type) as total_qty,
sum(case when trx_type = 'PDO' and do_date <= '20071231' then qtt_out end) over(partition by trx_type) as total_qtt_out
from Testing
)a
where wasa = 1 and do_date <= '30001231'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 10:06:23

quote:
Originally posted by waterduck

anyone? i think sifu slept

Hope can help...but advise to wait pros with confirmation...

Almost . . . . it has been a long day

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-27 : 10:22:10

thx sifu....today just learnt that over can use at sum...lol

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources