Que:sum all (table:MF) SUM(QUANTITY),SUM(AMT) use group with rollup and having grouping

 NO         DESCRIPTION specification   UOM  QUANTITY   Price   AMT 
    01        A1                1*3    pcs       10         2      20 
    02        A2                2*3    pcs       15         2      30 
    03        B1                3*4    pcs      6           3      18 
    04        B2                4*9    pcs      5           3      15

i Want to group by combined results are as follows,(use group with rollup and grouping):

NO         DESCRIPTION specification   UOM  QUANTITY      Price       AMT 
01        A1                1*3         PCS       10          2       20 
02        A2                2*3         pcs       15          2      30 
03        B1                3*4         pcs       6           3      18 
04        B2                4*9         pcs       5           3      15
SUMALL:                                          36                 83

--SQL create table

create table mf(NO varchar(5),description varchar(10),specification varchar(10),UOM varchar(5),Quantity int,price int,AMT int)

insert into mf 
 select '01','A1','1*3','PCS',10,2,20 union all
 select '02','A2','2*3','PCS',15,2,30 union all
 select '03','B1','3*4','PCS',6,3,18 union all
 select '04','B2','4*9','PCS',5,3,15

asked 15 Dec '13, 08:22

mfkpie8's gravatar image

mfkpie8
273667075
accept rate: 12%


permanent link

answered 20 Dec '13, 07:28

Reimer%20Pods's gravatar image

Reimer Pods
4.5k384891
accept rate: 11%

Detection of NULLs using the GROUPING function ,But he didn't check how NULL only keep a line

(21 Dec '13, 22:58) mfkpie8

MICROSOFT SQL :

select case grouping(NO) when 1 then 'SUMALL:' else NO end 'NO',
       case grouping(NO) when 1 then '' else description end 'description',
       case grouping(NO) when 1 then '' else specification end 'specification',
       case grouping(NO) when 1 then '' else UOM end 'UOM',
       case grouping(NO) when 1 then sum(quantity) else max(quantity) end 'quantity',
       case grouping(NO) when 1 then '' else rtrim(price) end 'price',
       case grouping(NO) when 1 then sum(amt) else max(amt) end 'amt'
 from mf
 group by NO,description,specification,UOM,Price
 with rollup
 having grouping(NO)=1 or grouping(Price)=0

it's at SQL 2005 by i need use SYbase ASA

permanent link

answered 20 Dec '13, 06:53

mfkpie8's gravatar image

mfkpie8
273667075
accept rate: 12%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×11

question asked: 15 Dec '13, 08:22

question was seen: 2,339 times

last updated: 21 Dec '13, 22:58