Please help me to write the following sql. I have a table like this, with an amount column and date column

amount     date
-----------------------
100    -    2010-02-05
200    -    2010-02-05
50     -    2010-02-06
10     -    2010-02-06
10        2010-02-07

what I want is to write a sql to get total for each day. Ultimately my query should return something like this

amount     date
-----------------
300  -     2010-02-05
60   -     2010-02-06
10   -     2010-02-07

Please note that now it has group by dates and amounts are summed for that date.

RESOLVED -

This was my bad, even though I mention the date column as date here, my actual date column in postgres table was 'timestamp'. Once I changed it to get the date only, everything got working

my working sql 开发者_如何学JAVAis like this "select sum(amount), date(date) from bills group by date(date) "

thanks everyone (and I will accept the 1st answer as the correct answer since I can accept only one answer)

thanks again

sameera

Pretty basic group by statement.

SELECT SUM(table.amount), table.date
FROM table
GROUP BY table.date

http://www.w3schools.com/sql/sql_groupby.asp

Look into GROUPING by date, here: http://www.w3schools.com/sql/sql_groupby.asp

and look into SUM(), here: http://www.w3schools.com/sql/sql_func_sum.asp

You will need to use both of them.

Try this:

SELECT SUM(Amount), Date FROM Table
GROUP BY Date

SELECT SUM(amount) as amount_sum, date FROM table GROUP BY date;