16

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.

user1650361
  • 163
  • 1
  • 1
  • 4
  • Welcome to Stack Overflow! We encourage you to [research your questions](http://stackoverflow.com/questions/how-to-ask). If you've [tried something already](http://whathaveyoutried.com/), please add it to the question - if not, research and attempt your question first, and then come back. –  Sep 27 '12 at 16:05

2 Answers2

29

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

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • Thanks. This is close, however for some reason it is returning double the sum, i.e. 120 instead of 60. Also, it's listed in every row, not that it's a big deal. I thought there was a way using COALESCE to have it returned just once (I am NO SQL expert so I very well could be wrong) Thanks!!! – user1650361 Sep 05 '12 at 23:07
  • @user1650361 can you create a sqlfiddle with some additional sample data? Based on the limited data you provided it appears to be working. – Taryn Sep 05 '12 at 23:08
  • This works. Issue with my SQL statement was causing the wrong data. Thanks! – user1650361 Sep 06 '12 at 01:32
0
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...

user10135658
  • 1