From: | Emir Ibrahimbegovic <emir(dot)ibrahimbegovic(at)gmail(dot)com> |
---|---|
To: | chris(at)chriscurvey(dot)com |
Cc: | pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Inconsistent results postgresql |
Date: | 2014-07-31 12:44:30 |
Message-ID: | CABuViOxoc+uyQtA6uVcDFzc2OeWCfrbgV5B9fcL820hAdBAZ0A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thank you for your response guys.
On Wed, Jul 30, 2014 at 9:25 PM, Chris Curvey <chris(at)chriscurvey(dot)com> wrote:
>
>
>
> On Wed, Jul 30, 2014 at 8:41 PM, Emir Ibrahimbegovic <
> emir(dot)ibrahimbegovic(at)gmail(dot)com> wrote:
>
>> Hello all,
>>
>> I've got two queries which should produce the same results but they don't
>> for some reason, please consider these :
>>
>> SELECT date_trunc('day', payments.created_at) "day", SUM("payments"."amount") AS sum_id FROM "payments"
>> INNER JOIN "users" ON "users"."id" = "payments"."user_id" WHERE "payments"."currency" = 'gbp' AND (payments.refunded_date is null)
>> AND (users.deleted_at is null)
>> AND (users.subscribed_at between '2014-07-07 00:00:00.000000' and '2014-07-07 23:59:59.999999')
>> AND ("payments"."created_at" BETWEEN '2014-07-07 00:00:00.000000' AND '2014-07-07 23:59:59.999999')GROUP BY 1ORDER by 1 asc
>>
>> It produces this :
>>
>> day | sum_id------------------------------"2014-07-07 00:00:00" | 1863.85
>>
>> But when I try to group by day at looking at 30 days period with this
>> query :
>>
>> SELECT date_trunc('day', payments.created_at) "day", SUM("payments"."amount") AS sum_id FROM "payments"
>> INNER JOIN "users" ON "users"."id" = "payments"."user_id" WHERE "payments"."currency" = 'gbp' AND (payments.refunded_date is null)
>> AND (users.deleted_at is null)
>> AND (users.subscribed_at between '2014-06-30 00:00:00.000000' and '2014-07-30 23:59:59.999999')
>> AND ("payments"."created_at" BETWEEN '2014-06-30 00:00:00.000000' AND '2014-07-30 23:59:59.999999')GROUP BY 1ORDER by 1 asc
>>
>> It produces this (truncated to include the important data only) :
>>
>> day | sum_id------------------------------"2014-07-07 00:00:00" | 1898.84
>>
>> So looking for same date using different date range I get different
>> results, how is this even possible? Can I look at something else? I'm
>> really stuck here
>>
>> Thanks
>>
> Remove the sum (just select "payments.amount") and the GROUP BY and run
> your queries. You'll see that you're getting different rows included than
> you think you are.
>
>
> --
> I asked the Internet how to train my cat, and the Internet told me to get
> a dog.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2014-07-31 12:58:14 | Re: BDR Postgres |
Previous Message | David G Johnston | 2014-07-31 07:40:16 | Re: User-defined operator function: what parameter type to use for uncast character string? |