CODEWITHSUNDEEP
sqliteaggregate-functionsgroup-summaries
ayush varshney
ayush varshney

Reputation: 537

Adding summary row based on category in Sqlite

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

Upvotes: 2

Views: 1447

Answers (4)

CL.
CL.

Reputation: 180250

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;

Upvotes: 0

denny
denny

Reputation: 2274

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

Upvotes: 0

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Check This.

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

Upvotes: 2

ayush varshney
ayush varshney

Reputation: 537

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

Upvotes: -1

Related Questions