2

I have first query

IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL DROP TABLE #tmp1
select 
    IDX=ROW_NUMBER() over(order by A_ID)
    ,A = A_ID
    ,Number = COUNT(*)
    ,Value = sum(ammount)
into #tmp1
from #t
group by A_ID

Need to do union with

   select   
    IDX  = ''
    ,A = 'Total'
    ,SUM (Number)
    ,SUM(Value)
   from #tmp1

After UNION ALL I get dataset:

+--------+------------+-------------+--------+
|  IDX   |     A      |  Number     |  Value |
+--------+------------+-------------+--------+
|      1 |  PCX       |      12     |  22.00 |
|      2 |  TOC       |       3     |  89.00 |
|      0 | Total      |      15     | 111.00 |
+--------+------------+-------------+--------+

But I need:

    +--------+------------+-------------+--------+
    |  IDX   |     A      |  Number     |  Value |
    +--------+------------+-------------+--------+
    |      1 |  PCX       |      12     |  22.00 |
    |      2 |  TOC       |       3     |  89.00 |
    |============================================|
    |      3 | Total      |      15     | 111.00 |
    +--------+------------+-------------+--------+

How to insert entire row of '=' and continue row number of IDX ?

Version is 2014 Enterprise.

0
4

You could use GROUP BY GROUPING SETS and the GROUPING() function:

SELECT 
    idx = ROW_NUMBER() OVER (ORDER BY GROUPING(a_id), a_id),
    a = CASE WHEN GROUPING(a_id) = 0 THEN a_id ELSE 'Total' END, 
    number = COUNT(*),
    value = SUM(amount)
FROM #t
GROUP BY GROUPING SETS ((a_id), ()) 
ORDER BY idx ; 

In older versions (that don't have GROUPING SETS) you could use ROLLUP instead. In even older versions (or any version if you prefer a simpler query), you could split the query into 2 parts and UNION ALL with:

SELECT 
    idx = ROW_NUMBER() OVER (a_id),
    a = a_id, 
    number = COUNT(*),
    value = SUM(amount)
FROM #t
GROUP BY a_id

UNION ALL

SELECT 
    1 + COUNT(DISTINCT a_id),
    a_id, 
    COUNT(*),
    SUM(amount)
FROM #t

ORDER BY idx ; 

Tested at rextester.com

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.