Search code examples
sqlgroup-bysap-asesubtotal

SQL Get subtotal in each group

Good Morning in my timezone

My goal is :

TYPE |  CODE | PRICE | QUANTITY
A       10      34       1
A       11      20       2
A       15      17       2
A       Total   71       5
B       13      14       1
B       10      24       2
B      Total    38       3

I am using Sybase ASE version 15.5 so there is not the ROLLUP or CUBE operators. Is the cursor the only way i got ?

Thanks in advance Best Regards

Solution

  • You may use such a SQL statement with union all ( ordering is important for your aim ):

    select q.*
  from
  (
    select type, cast(code as char(5)) as code, price, quantity
      from tab
    union all
    select type, 'Total', sum(price), sum(quantity) 
      from tab
    group by type
  ) q
  order by type, code

    I'm a adding a Demo in SQL Server but syntax works for both.