Adding summary row based on category in Sqlite

Question

Is it possible to add summary in rows with group by: To be specific i am looking for the below output.

Main Table

Client|Billing
--------------
a     |   34
a     |   27
b     |   29
b     |   27
c     |   28
c     |   37

Output should look alike:

Client|Billing
--------------
a     |  34
a     |  27
Total |  61
b     |  29
b     |  27
Total |  56
c     |  28
c     |  37
Total |  65

Here the first total is the sum of billing for client a, similarly the next 2 totals are the sum of client b and c respectively.

i am able to achieve similar kind of requirement with the below code: select cast(Client as varchar(10)) Client, Billing from mydata union all select 'Total', sum(Billing) from mydata group by Client

however the total rows are coming at the end, like below :(

  Client|Billing
    a   |34
    a   |27
    b   |29
    b   |27
    c   |28
    c   |37
Total   |61
Total   |56
Total   |65

You can use WITH ROLLUP, but generally I think it's best to handle this kind of thing application code. — Strawberry, Mar 01 '17 at 09:23
correct, since sqlite does not provide the liberty to use Rollup, i am struggling with this kind of requirement. Any one can please help me, as this is very urgent for me. — ayush varshney, Mar 01 '17 at 09:28

Mr. Bhosale · Answer 1 · 2017-03-01T09:57:57.917

2

Check This.

    select * from mydata 
    union 
    select (Client ||'_Total') Client 
    ,SUM(Billing)sum from mydata 
    group by Client 
    order by Client

edited Mar 01 '17 at 09:57

answered Mar 01 '17 at 09:32

Mr. Bhosale

3,018
1
17
34

Thanks for showing me the another way to do it. though concat command does not works in sqlite however below code can do the task: select * from mydata union select (Client ||'_Total') Client ,SUM(Billing)sum from mydata group by Client order by Client – ayush varshney Mar 01 '17 at 09:42
@ayushvarshney I missed that u are using SQLLIte. ok but logic is same that you created query is too correct. still any help ask any time ayush. HND – Mr. Bhosale Mar 01 '17 at 09:59

denny · Answer 2 · 2017-03-01T10:47:11.097

0

I convert @mr.bhosale's answer in sqlite

try this

    select * from table_name
    union
    select Client || '_Total' Client ,SUM(Billing)sum from table_name
    group by Client
    order by Client

edited Mar 01 '17 at 10:47

answered Mar 01 '17 at 09:38

denny

2,084
2
15
19

score 0 · Answer 3 · answered Mar 01 '17 at 11:53

To order the "Total" columns correctly, you have to keep the client name so that you can sort by it later; the OrderNr column is used to sort the totals after the other rows of the same client:

SELECT DisplayName AS Client,
       Billing
FROM (SELECT Client AS DisplayName,
             Client,
             Billing,
             1 AS OrderNr
      FROM MyTable

      UNION ALL

      SELECT 'Total',
             Client,
             sum(Billing),
             2 AS OrderNr
      FROM MyTable
      GROUP BY Client)
ORDER BY Client,
         OrderNr;

score -1 · Answer 4 · answered Mar 01 '17 at 09:43

-1

select * from mydata 
  union
  select (Client ||'_Total') Client ,SUM(Billing)sum from mydata
  group by Client
  order by Client

answered Mar 01 '17 at 09:43

ayush varshney

517
7
20

2

While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Lynn Crumbling Mar 01 '17 at 20:17

Adding summary row based on category in Sqlite

4 Answers4