0

I have two very similar queries that are returning different totals. The first query, where the total [of subscription_payment.price] is calculated with PHP (and verified in Excel), is the same query that the second query is based off of.

First Query:

SELECT *,
       subscriptions_new.id AS subscription_id,
       plans_new.name AS plan_name,
       plans_new.guideid AS guideid,
       subscription_payment.date AS date,
       subscription_payment.renewal AS renewal,
       subscription_payment.price AS price,
       subscription_payment.price AS renewal_price
FROM transactions_new
JOIN accounts ON accounts.id = transactions_new.userid
JOIN subscriptions_new ON FIND_IN_SET(subscriptions_new.id, transactions_new.subscription_ids)
JOIN plans_pricing ON subscriptions_new.pricing_id = plans_pricing.id
JOIN subscription_payment ON subscription_payment.subscription_id = subscriptions_new.id
JOIN plans_new ON plans_new.id = plans_pricing.plan_id
WHERE
  subscription_payment.date >= 1417410000
  AND subscription_payment.date <= 1418187540
  AND subscription_payment.deleted != 1
GROUP BY subscriptions_new.id
ORDER BY plan_code DESC

Second Query:

SELECT SUM(subscription_payment.price) AS total,
       COUNT(*) AS qty,
       plan_code AS plan_code
FROM transactions_new
JOIN accounts ON accounts.id = transactions_new.userid
JOIN subscriptions_new ON FIND_IN_SET(subscriptions_new.id, transactions_new.subscription_ids)
JOIN plans_pricing ON subscriptions_new.pricing_id = plans_pricing.id
JOIN subscription_payment ON subscription_payment.subscription_id = subscriptions_new.id
JOIN plans_new ON plans_new.id = plans_pricing.plan_id
WHERE
  subscription_payment.date >= 1417410000
  AND subscription_payment.date <= 1418187540
  AND subscription_payment.deleted != 1
GROUP BY plan_code
ORDER BY plan_code DESC

In the second query,total is not matching what has been calculated from adding all of the records in the first query.

Any help is appreciated. Also note that the GROUP BY makes no difference when calculating the totals (so it's okay for them to be different - that's not causing the issue).

8
  • Which total has a larger value? Dec 9, 2014 at 19:56
  • @JohnM - Thanks for the response. The second query has a higher total.
    – Ryan M
    Dec 9, 2014 at 19:58
  • 4
    That's because you're using MySQL's non-deterministic grouping behavior which does not align with the SQL Standard. If you want consistent results you need to have all columns in your SELECT either be in the GROUP BY or an aggregate function.
    – JNK
    Dec 9, 2014 at 19:58
  • @JNK - Is there a way to group on total or qty (it's not letting me I'm assuming because they're aliases using SUM() and COUNT()). Also what is an aggregate function in MySQL and do you have any pointers on how I could use it here? Thanks!
    – Ryan M
    Dec 9, 2014 at 20:11
  • Which table does the plan_code belong to? Dec 9, 2014 at 21:38

1 Answer 1

1

I followed @ypercube's suggestion and just rewrote both queries - turns out there wasn't a relationship in the database that was needed to tie two tables together, and when there were multiple relationships on a set of columns MySQL did what @JNK said and was non-deterministic and grouped inconsistently.

If you run into something like this, check your table relationships, especially if there are multiple possibilities with what you're joining on and you only need to be joining on one.

Thanks for the help everyone.

Not the answer you're looking for? Browse other questions tagged or ask your own question.