CODEWITHSUNDEEP
sqlsqlite
user1650361
user1650361

Reputation: 193

SQLite: Get Total/Sum of Column

I am using SQLite and am trying to return the total of one column buy_price in the column TOTAL while at the same time returning all of the data. I do not want/need to group the data as I need to have the data in each returned row.

id    date       pool_name    pool_id    buy_price  TOTAL
 1    09/01/12   azp          5          20
 2    09/02/12   mmp          6          10
 3    09/03/12   pbp          4          5
 4    09/04/12   azp          7          20
 5    09/05/12   nyp          8          5             60

When I include something like SUM(buy_price) as TOTAL it only returns one row. I need all rows returned along with the total of all buy_price entries.

Upvotes: 19

Views: 41072

Answers (2)

user10135658
user10135658

Reputation: 11

Select * from yourtable
union
select 'Total',
  ' ',
  ' ',
  ' ',
  sum(buy_price)
from yourtable

you can add a row on the bottom like this instead of adding a new column...

Upvotes: 1

Taryn
Taryn

Reputation: 247630

It sounds like this is what you are looking for:

select id,
  dt,
  pool_name,
  pool_id,
  buy_price,
  (select sum(buy_price) from yourtable) total
from yourtable

see SQL Fiddle with Demo

Upvotes: 31

Related Questions