SUM in LEFT JOIN query

Question

I have two tables:

Table products
ID    Name         Base    
1     Product_1    5
2     Product_2    4

Table orders
ID    Product_ID    
1     1            
2     1           
3     2   

I'm using this query:

SELECT products.ID, products.Base, COUNT(orders.ID) AS Counter 
FROM products 
LEFT JOIN orders ON products.ID = orders.Product_ID 
GROUP BY products.ID

to get:

ID    Base    Counter
1     5       2
2     4       1

What I want to do now is to write a query that's going to return a table like one above but with extra column SUM - sum of Base and Counter:

ID    Base    Counter    SUM
1     5       2          7
2     4       1          5

Is it possible to achieve this result using single query? I'd also like to order my results by SUM column.

Answer 1

SELECT products.ID, products.Base, COUNT(orders.ID) AS Counter, products.Base + COUNT(orders.ID) as `sum`
FROM products 
LEFT JOIN orders ON products.ID = orders.Product_ID 
GROUP BY products.ID, products.Base
ORDER BY `sum` DESC

Always include ALL non-aggregating columns in the group by clause. See https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

As you will see in that reference, MySQL has a non-standard extension to group by that allows for a relaxed query syntax. However the results rely on MySQL making arbitrary choices to achieve the groupings. Additionally if the ONLY_FULL_GROUP_BY setting changes at any point if you follow SQL syntax rules your query will remain valid if you include all non-aggregating columns in the group by clause.

Answer 2

Don't overthink it. Just add them together with a plus sign.

SELECT products.ID, products.Base, COUNT(orders.ID) AS Counter,
products.Base + COUNT(orders.ID) as `SUM`
FROM products 
LEFT JOIN orders ON products.ID = orders.Product_ID 
GROUP BY products.ID
ORDER BY `SUM`

Note: "SUM" is the name of an aggregate SQL function so you'll need to surround it in backticks in order to use it as a column alias. Consider using a different name.