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!

Answer 1

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 |