Register / Login
this forum made possible by our volunteer staff, including ...
Stephan van Hulst
JDBC and Relational Databases
posted 3 years ago
select 'Normal' AS type0, count(c.CustomerCode) AS cnt, c1.OrderCode, IFNULL(sum(c1.NetSales),0) AS NetSales, date(c1.CustomerCreationDate, 'unixepoch', 'localtime') from customer c LEFT JOIN (select c.*, o.*, od.* from customer c LEFT JOIN orders o ON (c.CustomerCode = o.CustomerCode) LEFT JOIN order_details od ON (o.OrderCode = od.OrderCode) group by c.CustomerCode having NetSales < 5000) c1 where strftime('%Y-%m', date(c1.CustomerCreationDate, 'unixepoch', 'localtime')) = strftime('%Y-%m', date('now', '-0 MONTH'))
I tried to filter the normal customer 1st, then the customer created in this month
The first subquery returns no sets.
I seem to be having trouble when the order details table has got nothing in it
select c.*, o.*, IFNULL(sum(od.NetSales),0) AS NetSales from customer c LEFT JOIN orders o ON (c.CustomerCode = o.CustomerCode) LEFT JOIN order_details od ON (o.OrderCode = od.OrderCode) group by c.CustomerCode having NetSales < 5000
By further breakdown of the problem, how can this return something
when order details has nothing inside, thus a 0 NetSales
I solved it by adding another union, making the no sales customer in the normal's set
, while having the VIP unchanged
It is sorta covered in the
JavaRanch Style Guide
Boost this thread!
I'd like to select a customer that never made a sale
SQL Date Wrap around question
Same query, different results
How to get the last purchased product?
SQLite abs yields results of nulls