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).
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 theGROUP BY
or an aggregate function.plan_code
belong to?