# Optimization of complicated sums and counts in related tables

I have a `categories` table, which have two columns, `id` and `name`.

I have a `types` table, which have three columns, `id`, `category_id` and `points`.

I have a `products` table, which have three columns, `id`, `type_id` and `customer_id`.

This means each product has a type, and each type has a category. One category has many types, one type has many products.

I am using MySQL and PHP.

From PHP, given a `customer_id`, I want to know how much points that customer has in each category separately (and display a table to the user, for example).

My solution: I would first do a query to find what are all category ids, then for each `category_id` I would find all types inside that category (that is, all types with the given `category_id`); then for each `type_id` I would count how many products have both that `type_id` and the given `customer_id`, and I would multiply that amount for the points of the given `type_id`. Adding all the results for each `type_id`, I have in the end the total points the given customer has on that `category_id`. And as I said, I would have to do this for each `category_id`. My solution will work, but seems to me that it would be VERY slow. I am looking for advice on how to solve this problem in a better way - with less queries, or faster queries, or both. The way it is now, it can potentially do as many queries as the amount of categories.

Example:

``````/* TABLE: categories */

name           id
A              1
B              2
C              3

/* TABLE: types */

points         id          category_id
1000             1                1
500              2                1
200              3                3
50               4                2
20               5                3
1                6                3

/* TABLE: products */

id           type_id          customer_id
1               2                   13
2               4                   13
3               5                   13
4               6                   13
5               5                   13
6               3                   13
7               4                   13
8               5                   13
9               6                   13
``````

If the given customer_id is 13, I want get something like the following:

500 total points in category A 100 total points in category B 262 total points in category C

1. What is a good way to do this without changing how my database is structured?

2. Is there a good way to change my database design to allow a better way to do this? (of course I have other fields in the tables that I omitted for simplicity).

• Learn about JOINs and GROUP BY. It can be done with a single query for one user (and even for all users at the same time if you ever needed that). Oct 1, 2015 at 18:45
• I'm sure the query you have attempted has some `JOIN`s and a `GROUP BY` in it. If you post it, we may be able to help you improve it if it doesn't do what you expect it to. Oct 1, 2015 at 18:46
• Thanks, with Max Vernon's answer I could understand perfecly how JOINs and GROUP BYs fit in my problem. Thanks for all the help. Oct 4, 2015 at 3:37

Your question shows you are thinking in a procedural way, however you need to think in a "set-based" way.

``````SELECT p.customer_id
, c.name
, sum(t.points)
FROM products p
INNER JOIN types t ON p.type_id = t.id
INNER JOIN categories c ON t.category_id = c.id
GROUP BY p.customer_id
, c.name;
``````

Essentially, this should show you a list of customer_id, category name, and the total points each customer has in each category. It accomplishes this through the use of an aggregate function (`SUM`) along with the `GROUP BY` clause.

(please take a look at my answer here for information about naming your `ID` columns)

• Awesome!! This worked perfectly. And I did not just copy-paste, I learned about INNER JOIN and GROUP BY so I could understand what I'm doing. Perfect. I added a WHERE clause and an ORDER BY as well, now I have exactly what I needed, and I am impressed with MySQL's capability of doing this. Indeed, thinking in a "set-based" way is much different from everything I've been used to, and worked great. Thank you so much. Oct 4, 2015 at 3:35