WITH ROLLUP - SQL Server Forums

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)

WITH ROLLUP

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-15 : 22:14:09

[code]SELECT a.st_code,
a.st_desc,
SUM(a.total_rm)as total_rm,
SUM(a.total_cn)as total_cn,
SUM(a.qty)as qty,
date
FROM (SELECT m.st_code, m.st_desc, th.total_rm*th.forex_rate as total_rm, th.total_cn*th.forex_rate as total_cn, th.qtt_out - th.quantity as qty, th.date
FROM st_mast m
inner join
(SELECT st_code, t.trx_type, t.qtt_out, t.quantity, t.total_price, h.date,
CASE WHEN h.forex_rate = 0 then 1 else h.forex_rate END as forex_rate,
CASE WHEN t.trx_type IN('CN','CNC','GRO') then 0 else t.total_price END as total_rm,
CASE WHEN t.trx_type IN('CN','CNC','GRO') then t.total_price else 0 END as total_cn
FROM st_trx t
right outer join
(SELECT CASE WHEN trx_type in('DO','CDO','DOL') then do_no
WHEN trx_type in('INV','CS','CN','DN','POS','INC','CNC') then in_no END as ref_no,
CASE WHEN trx_type in('DO','CDO','DOL') then (CASE WHEN in_date IS NULL or in_date='' then do_date else in_date END)
WHEN trx_type in('INV','CS','CN','DN','POS','INC','CNC') then in_date END as date,
forex_rate,
trx_type
FROM st_head)h on t.trnx_ref = h.ref_no and t.trx_type=h.trx_type
WHERE date >= '20070101' and date < '20080101') AS th on m.st_code=th.st_code
WHERE m.st_code>='0' and m.st_code<='2')a
GROUP BY a.st_code, st_desc, date
WITH ROLLUP[/code]
Result
[code]st_code st_desc total_rm total_cn qty date
------------------------- --------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
1 0.0000000000 0.0000000000 0.0000 2007-01-17 00:00:00.000
1 0.0000000000 0.0000000000 4.0000 2007-04-17 00:00:00.000
1 0.0000000000 0.0000000000 4.0000 NULL
1 NULL 0.0000000000 0.0000000000 4.0000 NULL
[/code]
Expected result
[code]
st_code st_desc total_rm total_cn qty date
------------------------- --------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
1 0.0000000000 0.0000000000 2.0000 2007-04-17 00:00:00.000
1 0.0000000000 0.0000000000 2.0000 2007-04-17 00:00:00.000
1 0.0000000000 0.0000000000 0.0000 2007-01-17 00:00:00.000
1 0.0000000000 0.0000000000 0.0000 2007-01-17 00:00:00.000
1 NULL 0.0000000000 0.0000000000 4.0000 NULL
[/code]

I think it confused me, 1st time using rollup

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-15 : 23:23:30

That's the behavior of roll up. refer to BOL on the GROUP BY section.

You can still use GROUP BY with ROLL UP with a derived table to achieve what you want.


select *
from   (
           < your existing query here>
       ) as d
where  d.st_desc  is null
or     d.[date]   is not null

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-15 : 23:30:13

ooo icic, it also group the date together T.T cant use roll up thx khtan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources