sum() does not work properly in MySQL query with multiple joins (group by does not work as expected)

Question

I have tables Orders, Networks and Users and I need to get total orders count and total sum for orders for every user, and users count in the same network that every user have.

SQLFiddle with sample data: http://sqlfiddle.com/#!9/dcbeea/1

users.userid, orders.id - unique primary keys.

In this example check user #24 row: values for total_orders, total_revenue, network_users are not correct.

Current results for user #24: total_orders: 6, total_revenue: 350, network_users: 6.

Expected results for user #24: total_orders: 3, total_revenue: 175, network_users: 2.

This is SQL request:

SELECT u.*,
   count(o.id) AS total_orders,
   sum(o.total) AS total_revenue,
   count(un.userid) as network_users /* Users count in same network */
FROM users u
LEFT JOIN orders o ON o.userid=u.userid
LEFT JOIN users am ON u.ownerid = am.userid
LEFT JOIN users bdr ON u.bdrid = bdr.userid
LEFT JOIN networks n ON u.networkid = n.networkid
LEFT JOIN users un ON n.networkid=un.networkid
GROUP BY u.userid
ORDER BY u.userid DESC;

ISSUE 1: total_orders and total_revenue here return incorrect values (more than it should, looks like it summed few times because of networks table join).

I can fix total_orders by adding distinct - count(distinct(o.id)) AS total_orders, however this does not work for sum because I can't set to sum total only by disctinct ID's, as I see there is no way to set this in SQL.

You can see issue in SQLFiddle example - user #24 should have total_revenue = 175, however you see it calculated as 350. As I see this happens because two different users associated with the same network (Network #1) that user #24 have.

ISSUE 2: count(un.userid) as network_users - this does not work properly if I don't add count(disctinct(un.userid)) as network_users. Without 'distinct' this show me overall networks count as I see (and not a overall users count in network with the same networkid as current user have). In SQL example 'network_users' for user id #24 should be 2 (because only 2 users in this network), however I see 6 in results.

QUESTION: How to change SQL request to get correct expected mathematically results?

Answer 1

One way: Count the network_users in a subselect (subquery in the SELECT clause)

SELECT u.userid, 
   count(o.id) AS total_orders,
   coalesce(sum(o.total), 0) AS total_revenue,
   (
     SELECT count(*)
     FROM users un     
     WHERE un.networkid = u.networkid
   ) as network_users
FROM users u
LEFT JOIN orders o ON o.userid=u.userid
GROUP BY u.userid, u.networkid
ORDER BY u.userid DESC;

Result:

| userid | total_orders | total_revenue | network_users |
| ------ | ------------ | ------------- | ------------- |
| 40     | 1            | 75            | 1             |
| 37     | 0            | 0             | 2             |
| 33     | 0            | 0             | 1             |
| 24     | 3            | 175           | 2             |

View on DB Fiddle

Another way: Do "preaggregation" in a derived table (subquery in the FROM clause)

SELECT u.userid,
  count(o.id) AS total_orders,
  coalesce(sum(o.total), 0) AS total_revenue,
  u.network_users
FROM (
  SELECT u.userid, count(un.userid) as network_users
  FROM users u
  LEFT JOIN users un ON un.networkid = u.networkid
  GROUP BY u.userid
) u
LEFT JOIN orders o ON o.userid=u.userid
GROUP BY u.userid
ORDER BY u.userid DESC;

Result:

| userid | network_users | total_orders | total_revenue |
| ------ | ------------- | ------------ | ------------- |
| 40     | 1             | 1            | 75            |
| 37     | 2             | 0            | 0             |
| 33     | 1             | 0            | 0             |
| 24     | 2             | 3            | 175           |

View on DB Fiddle

Some notes:

• Make sure userid is UNIQUE or PRIMARY KEY in the users table. Otherwise you can get an error starting from MySQL 5.7.
• I removed the JOINs with users am and users bdr. You are not using them in your query. You can put them back, if you want to select anything from them.
• I also removed the JOIN with the networks table. You can just join the users table with itself using the networkid column.
• Use COALESCE() for SUM() to convert NULL to 0.

Why your query failed to return the expected result? Because you are joining orders from a user with all other users from the same network. Thus the order count and the total amount are multiplied by the number of users in the same network.

Answer 2

Look at it this way. When doing a JOIN, first all combinations of the rows from the tables are put together into a big temp table (after filtering out any that don't apply).

Then aggregates such as COUNT() and SUM() are computed against this big table.

Usually this is not correct. And usually the cure is to first devise a query that uses the minimum number of tables needed to get the correct sum from the intermediate table. Then do any more JOINs you may need.

An alternative (sometimes) is to employ subqueries, either to do the aggregation or to provide the equivalent of JOINs.

Sometimes the query will look like this:

SELECT ...
    FROM ( SELECT key, COUNT(*), SUM(..) FROM .. GROUP BY .. ) AS a
    JOIN b  ON ...
    JOIN c  ON ...

Answer 3

Adding a third option to @Paul's excellent answer

You could do your counts separately, put them together using UNION ALL, and then SUM these rows

  SELECT agg.userid,
         SUM(agg.order_count) AS total_orders,
         SUM(agg.revenue_sum) AS total_revenue,
         SUM(agg.network_user_count) AS network_users

    FROM (

       /** Orders and Revenue */
    SELECT u.userid,
           COUNT(o.id) AS order_count,
           SUM(o.total) AS revenue_sum,
           0 AS network_user_count
      FROM users u
 LEFT JOIN orders o ON o.userid=u.userid
  GROUP BY u.userid 

     UNION ALL

       /** Network Users */
    SELECT u.userid, 
           0 AS order_count,
           0 AS revenue_sum,
           COUNT(un.userid) AS network_user_count
      FROM users u
 LEFT JOIN users un 
        ON un.networkid = u.networkid
  GROUP BY u.userid

         ) agg 

GROUP BY agg.userid
ORDER BY agg.userid DESC;

The inner query agg will give results like

| userid | order_count | revenue_sum | network_user_count |
| ------ | ----------- | ----------- | ------------------ |
| …
| 24     | 3           | 175         | 0                  |
| 24     | 0           | 0           | 2                  |
| …

And the outer query will then combine these rows with SUMs

It's slightly more long winded, but I have used this method in some of our projects