[Solved] MySQL: Why does query give NULL as a result of SUM?

EverSQL Database Performance Knowledge Base

MySQL: Why does query give NULL as a result of SUM?

Database type:

Columns in the table orders are as follows:

order_id, order_nr, created_at, order_value, payment_method

I would like to group orders from 2014 by month and check sum of their values depending on payment method.

I got a query: 

SELECT 
  month(created_at),
  CASE WHEN payment_method = 'Cash' THEN sum(order_value) END as Cash,    
  CASE WHEN payment_method = 'CreditCard' THEN sum(order_value) END as CreditCard

FROM orders  
WHERE month(created_at) BETWEEN 5 AND 6 
AND year(created_at) = 2014 
GROUP BY month(created_at) 
ORDER BY order_value

Problem: For CreditCard the result is null.

What I did:
- I checked if there is any order where order_value is null - I didn't find any.
- I tried COALESCE(sum(order_value),0)
- I tried sum(IFNULL(order_value,0))
- I used this query: 

  SELECT 
    month(created_at) as m,
    CASE WHEN payment_method = 'CreditCard' THEN sum(order_value) END as CreditCard
  FROM orders
  AND month(created_at) BETWEEN 5 AND 6
  AND year(created_at) = 2014
  GROUP BY month(created_at), payment_method /*here is the difference*/
  ORDER BY month(created_at) ASC

Gives me results like:

m | CreditCard
---------------
5 | NULL
5 | NULL
5 | 13256
6 | NULL
6 | NULL 
6 | 15356

Can you please explain to me
A. from where does null come from?
B. How can I avoid it?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Using Date Functions In Conditions (query line: 11): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. An alternative way is to use a range condition instead of a function call.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  3. Index Function Calls Using Generated Columns (modified query below): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index to optimize the search. Creating and indexing a generated column (supported in MySQL 5.7) will allow MySQL to optimize the search.
Optimal indexes for this query:
ALTER TABLE `orders` ADD INDEX `orders_idx_month_at` (`month_created_at`);
The optimized query:
SELECT
        month(orders.created_at),
        CASE 
            WHEN orders.payment_method = 'Cash' THEN sum(orders.order_value) END AS Cash,
CASE 
    WHEN orders.payment_method = 'CreditCard' THEN sum(orders.order_value) END AS CreditCard 
FROM
orders 
WHERE
orders.month_created_at BETWEEN 5 AND 6 
AND orders.created_at BETWEEN '2014-01-01 00:00:00' AND '2014-12-31 23:59:59' 
GROUP BY
orders.month_created_at 
ORDER BY
orders.order_value

Related Articles



* original question posted on StackOverflow here.