CODEWITHSUNDEEP
ruby-on-railspostgresql
pyfl88
pyfl88

Reputation: 1710

How to group date by day and sum in postgres?

I got this model:

[#<Account:0x007fcf32153098
  id: 1,
  profit: 100,
  user_id: 1,
  created_at: Sun, 15 Nov 2015 02:27:43 UTC +00:00,
  updated_at: Sun, 15 Nov 2015 02:27:43 UTC +00:00>,
 #<Account:0x007fcf32152df0
  id: 2,
  profit: 500,
  user_id: 1,
  created_at: Sun, 16 Nov 2015 15:05:07 UTC +00:00,
  updated_at: Sun, 15 Nov 2015 15:05:07 UTC +00:00>,
 ]

And for now I got this to group them in date:

Account.all.group_by{|a| a.created_at.strftime("%Y-%m-%d")}

{"2015-11-15"=>
  [#<Account:0x007fcf3247b1a8
    id: 1,
    profit: 100,
    user_id: 1,
    created_at: Sun, 15 Nov 2015 02:27:43 UTC +00:00,
    updated_at: Sun, 15 Nov 2015 02:27:43 UTC +00:00>],
"2015-11-16"=>
   [#<Account:0x007fcf3247afc8
    id: 2,
    profit: 500,
    user_id: 1,
    created_at: Sun, 16 Nov 2015 15:05:07 UTC +00:00,
    updated_at: Sun, 15 Nov 2015 15:05:07 UTC +00:00>]}

My question is: How can I group them and at the same time sum the profit together if there are more than one record for that day? Seems like I can't use sum(:profit) with postgres?

Upvotes: 1

Views: 680

Answers (1)

rlarcombe
rlarcombe

Reputation: 2986

I think you can just do this simply with:

Account.order("DATE(created_at)").group("DATE(created_at)").sum(:profit)

Upvotes: 3

Related Questions