我想要無NetSales within 180日 to 90 日出零...

luckiejacky

想請問如果沒有Sales within that period, how do I get a 0 for that customer?

1. select c.CustomerID, o.PurchaseDateTime, IFNULL(sum(round(od.NetSales,2)),0) as NetSales180
   
2.               from customers AS c LEFT JOIN orders AS o
   
3.               ON (c.CustomerID = o.CustomerID)
   
4.               LEFT JOIN order_details AS od ON (o.OrderID = od.OrderID)
   
5.               where DATE(o.PurchaseDateTime) BETWEEN CURDATE() -  INTERVAL 180 DAY AND CURDATE() - INTERVAL 90 DAY
   
6. 
   
7.                                   group by c.CustomerID

複製代碼

Customer ID      NetSales180
1                            0
2                            3000.0
3                            0

現在我一條row 都無....
THX

henrywho

1st of all, as a rule of thumb:
(a) ifnull() before sum()
(b) sum() before round()

hence, it should be round(sum(IFNULL(od.NetSales,0)),2)

luckiejacky

Thanks henry
Actually, No customers have sales in that period, but Still i don't get zeros
rows from what you suggested...

rabbit82047

照常理句 SQL 會出 PurchaseDateTime is not specified in group by clause
catch SQLException 睇下

仲有就係, 既然要攞 sum over time, 點解仲要 PurchaseDateTime?

luckiejacky

照常理句 SQL 會出 PurchaseDateTime is not specified in group by clause
catch SQLException 睇下

仲有 ...
rabbit82047 發表於 2013-9-17 13:49

當我block out段 Date selection code
I get this

rabbit82047

1. select c.CustomerID, min(o.PurchaseDateTime), max(o.PurchaseDateTime), round(sum(coalesce(od.NetSales,0)),2) as NetSales180
   
2. from customers AS c LEFT JOIN orders AS o ON (c.CustomerID = o.CustomerID)
   
3. LEFT JOIN order_details AS od ON (o.OrderID = od.OrderID) and
   
4.                 DATE(o.PurchaseDateTime) BETWEEN CURDATE() - INTERVAL 180 DAY AND CURDATE() - INTERVAL 90 DAY
   
5. group by c.CustomerID

複製代碼

理論上可以完全忽略 where clause, 將個 range condition 寫落去 join condition
===
唔 check 都唔知 mariadb/mysql 有所謂 group by extension
http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

個人覺得與其俾 db 抽 PurchaseDateTime
倒不如加 min, max, 出黎既 result 先有意思
或者乾脆唔要 PurchaseDateTime 好過

franklee006

Ching 你做個 ordering system 有冇咩Demo 比我地睇睇
我上個月留意到你開始做有少少好寄你依加做成點

justlazy

Ching 你做個 ordering system 有冇咩Demo 比我地睇睇
我上個月留意到你開始做有少少好寄你依加做成點 ...
franklee006 發表於 2013-9-17 23:44

好奇 +1

henrywho

Actually, No customers have sales in that period, but Still i don't get zeros
rows from what you suggested.
luckiejacky 發表於 2013-9-17 13:30

of course, bcoz i was fixing your another problem which is more fundamental  :P