SQL selecting last month values if none are used still select them

Question

SELECT COUNT(wo.id), wm.*         
FROM webshop_orders as wo         
LEFT JOIN webshop_merchants as wm ON wo.merchant_id = wm.id         
LEFT JOIN webshop_merchant_order mo ON     mo.merchant_id = wm.id AND mo.language_id = 1
WHERE   (mo.hidden = 0) AND wm.status = 1         
AND DATE(wo.created_at) > CURDATE() - INTERVAL 1 MONTH         
GROUP BY wo.merchant_id         
ORDER BY COUNT(wo.id) DESC

I am selecting last month orders most used payment method and filtering the most used ones at the top of the list on the payment method page. The only problem i am now facing is that when a payment method doesn't get used for example an entire month it will skip the payment method and not show it.

My question is does somebody have a good example of this kind of problem because i cant find a good one on stackoverflow.

https://stackoverflow.com/questions/1046865/mysql-select-all-dates-in-a-range-even-if-no-records-present, https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range, https://stackoverflow.com/questions/36558715/fill-missing-dates-in-mysql-query-range — CBroe, Jun 22 '23 at 09:07
Remember that if you count(wo.id) and wo is your left table, then even if right table return null (no match), still count would be 1. So use the count of right table where you want to count 0 if there is no match. — Utsav, Jun 22 '23 at 09:28

dylan0344 · Accepted Answer · 2023-06-23T09:27:13.850

I have made some changes to my query and it works know.

SELECT wm.*,
        (SELECT COUNT(wo.id) as order_count FROM webshop_orders AS wo WHERE wo.merchant_id = wm.id AND DATE(wo.created_at) > CURDATE() - INTERVAL 1 MONTH AND wo.language_id = %d) as order_count
        FROM webshop_merchants AS wm
        LEFT JOIN webshop_merchant_order mo ON mo.merchant_id = wm.id AND mo.language_id = %d
        WHERE
        mo.hidden = 0
        AND wm.status = 1
        ORDER BY order_count DESC

results

score 0 · Answer 2 · answered Jun 22 '23 at 11:51

0

Have you tried changing the first WHERE statement ((mo.hidden = 0))with:

(mo.hidden = 0 OR mo.hidden IS NULL)

answered Jun 22 '23 at 11:51

Lasha Vasadze

1
1

SQL selecting last month values if none are used still select them

2 Answers2