Re: Inconsistent results postgresql - Mailing list pgsql-general

On Wed, Jul 30, 2014 at 8:41 PM, Emir Ibrahimbegovic <emir.ibrahimbegovic@gmail.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 1
ORDER 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 1
ORDER 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.