Include Summation Row with Group By Clause

Question

Query:

SELECT aType, SUM(Earnings - Expenses) "Rev"
FROM aTable
GROUP BY aType
ORDER BY aType ASC

Results:

| aType | Rev   |
| ----- | ----- |
| A     | 20    |
| B     | 150   |
| C     | 250   |

Question: Is it possible to display a summary row at the bottom such as below by using Sybase syntax within my initial query, or would it have to be a separate query altogether?

| aType | Rev   |
| ----- | ----- |
| A     | 20    |
| B     | 150   |
| C     | 250   |
=================
| All   | 320   |

I couldn't get the ROLLUP function from SQL to translate over to Sybase successfully but I'm not sure if there is another way to do this, if at all.

Thanks!

Thanks rs. I receive errors: "Function 'ROLLUP' not found. If this is a SQLJ function or SQL Function, use sp_help..." — Matt Rowles, Feb 11 '13 at 23:01

Gordon Linoff · Answer 1 · 2013-02-12T14:12:25.967

1

Not all versions of Sybase support ROLLUP. You can do it the old fashioned way:

with t as 
    (SELECT aType, SUM(Earnings - Expenses) "Rev"
     FROM aTable
     GROUP BY aType
    )
select t.*
from ((select aType, rev from t) union all
      (select NULL, sum(rev))
     ) t
ORDER BY (case when atype is NULL then 1 else 0 end), aType ASC

This is the yucky, brute force approach. If this version of Sybase doesn't support with, you can do:

select t.aType, t.Rev
from ((SELECT aType, SUM(Earnings - Expenses) "Rev"
       FROM aTable
       GROUP BY aType
      ) union all
      (select NULL, sum(rev))
     ) t
ORDER BY (case when atype is NULL then 1 else 0 end), aType ASC

This is pretty basic, standard SQL.

edited Feb 12 '13 at 14:12

answered Feb 12 '13 at 01:48

Gordon Linoff

1,242,037
58
646
786

This still doesn't work, even with the attached ; at the front as is the case in SQL (http://stackoverflow.com/questions/13357366/incorrect-syntax-near-the-keyword-table-when-i-declare-a-table-variable). Further more, I've attempted to use this using a temporary table and still no good :/ – Matt Rowles Feb 12 '13 at 03:47
1

@Matt . . . How frustrating. My guess is that your version of Sybase does not support the `with` statement. – Gordon Linoff Feb 12 '13 at 14:12
It looks that way! Admin control is out of my hands also, thanks for help though mate, appreciated. – Matt Rowles Feb 12 '13 at 22:36

Gopal Sanodiya · Answer 2 · 2013-02-12T11:52:46.883

May be you can work out with compute by clause in sybase like:

create table #tmp1( name char(9), earning int , expense int) 
insert into #tmp1 values("A",30,20)
insert into #tmp1 values("B",50,30)
insert into #tmp1 values("C",60,30)

select name, (earning-expense) resv from #tmp1
group by name
order by name,resv
compute sum(earning-expense)

OR

select name, convert(varchar(15),(earning-expense)) resv  from #tmp1
group by name
union all
SELECT "------------------","-----"
union all
select "ALL",convert(varchar(15),sum(earning-expense)) from #tmp1

Thanks, Gopal

score 1 · Accepted Answer · answered Feb 12 '13 at 11:10

Have you tried just using a UNION ALL similar to this:

select aType, Rev
from
(
  SELECT aType, SUM(Earnings - Expenses) "Rev", 0 SortOrder
  FROM aTable
  GROUP BY aType
  UNION ALL
  SELECT 'All', SUM(Earnings - Expenses) "Rev", 1 SortOrder
  FROM aTable
) src
ORDER BY SortOrder, aType

See SQL Fiddle with Demo. This gives the result:

| ATYPE | REV |
---------------
|     A |  10 |
|     B | 150 |
|     C | 250 |
|   All | 410 |

Include Summation Row with Group By Clause

3 Answers3