Q:

Order products by sales within 30 days followed by those without sales

I'm trying to fetch a list of products ordered by the sold quantity within the last 30 days. 
How would I make the following query also order by the sold quantity in the last 30 days and having them followed by the products that have not sold anything in those 30 days?
I need those with a sale within the last 30 days first and then the products without any sales after.
The field with the sale date is a datetime field called orders.sold_time
This is my current query:
SELECT    pd.products_id AS id, pd.products_name AS name
FROM      products_description pd
LEFT JOIN products ON pd.products_id = products.products_id
LEFT JOIN orders_products ON pd.products_id = orders_products.products_id
LEFT JOIN orders ON orders_products.orders_id = orders.orders_id
WHERE     pd.c5_dataset = 'DAT'
AND       products.products_status =1
AND       pd.language_id =4
AND       NOT EXISTS (
              SELECT 1
              FROM   products_description
              WHERE  products_id = pd.products_id
              AND    language_id = 10
          )
AND       orders_total.class = 'ot_total'
GROUP BY  pd.products_id
ORDER BY  count( orders_products.products_quantity ) DESC

A:

You can use a case when expression for that. The expression that gives you the sold quantity in the last 30 days is:
sum(case when orders.sold_time >= date_add(curdate(), interval -30 day) 
         then orders_products.products_quantity 
    end)

You can put this in the order by clause, and then you should decide what to order the articles by that have no sales in the last 30 days. This could for instance be the all-time sold quantity.
I would also use aliases for all your tables (like you did for the first one). So o for orders, op for orders_products.
So then your order by would look like:
order by sum(case when o.sold_time >= date_add(curdate(), interval -30 day) 
                  then op.products_quantity 
             end),
         sum(op.products_quantity)

To check the results, it would be good to also add those two expressions to the select list.
Some notes about the SQL:
As you have a non-null condition on products.products_status (=1) there is no good reason to use LEFT JOIN products. Instead use the more efficient INNER JOIN.
You would probably gain on efficiency if you would replace the NOT EXISTS condition with an INNER JOIN clause:
INNER JOIN products_description p2
       ON p2.products_id = pd.products_id
      AND p2.language_id = 10

provided there can be at most one description per product and language.

