Raw data before each group by

Question

Which queries should I execute in order to show raw data before every group by row? Is there a way to do that?

User   ReceiptNo   Price
-----  ----------  ------
A      876         100
A      877          50
                   **150**
B      960          60
B      961          40
                   **100**

Do you want this to be done using a SQL or you prefer doing this sub totals & grand totals in a reporting tools such as Tableau or SSRS? You should research about using ROLLUP in SQL. That should help you. — Raunak Jhawar, Sep 07 '16 at 11:42

score 1 · Accepted Answer · answered Sep 07 '16 at 11:41

1

Most database support standard group by modifiers such as rollup or grouping sets. If so, you can do:

select user, receiptno, sum(price) as price
from t
group by grouping sets ((user, receiptno), (user));

answered Sep 07 '16 at 11:41

Gordon Linoff

1,242,037
58
646
786

This works fine on my machine with Sql Server 2014 but on SQL Server 2008 throws error Incorrect syntax near 'sets'. – kk_nou Sep 08 '16 at 10:01
@kk_nou . . . I'm fairly sure SQL Server 2008 supports this syntax (for instance, https://blogs.msdn.microsoft.com/craigfr/2007/10/11/grouping-sets-in-sql-server-2008/). Perhaps you have a compatibility setting on that server set to an earlier version. – Gordon Linoff Sep 08 '16 at 10:46

score 0 · Answer 2 · answered Sep 07 '16 at 11:58

select user, receiptno, price, 1 as order_column 
from t
union all
select user, '' as receiptno, sum(price) as price, 2 as order_column 
from t
group by user

order by user, order_column

You can union results with raw data and add an artificial column to be used in ORDER BY to place the aggregate rows in the end of groups (after raw data).

Raw data before each group by

2 Answers2