Hello all.
I need help using query here.

I'm using this query and make grouping by date(month) with this query

SELECT p.name,MONTHNAME(s.date) as month,sum(CASE WHEN d.quantity is
NULL THEN 0 ELSE d.quantity END ) AS sumTotal from products p left
outer join (sales s right outer join sales_details d on
s.id=d.sale_id) on p.id=d.product_id WHERE S.date BETWEEN
CAST('2007-01-01 00:00:00' as Datetime) AND CAST('2007-07-30 23:59:59'
as Date) group by p.id,MONTHNAME(s.date);

In this query, it not shows the product that doesn't have record
in the date range on sales_detail
and one more thing, it is also doesn't show the month where there is
no record in sales on the date range.

For example if my products table have 5 records then with month 1-7
the query will give 35 records (5*7), where in some record the value
of sumTotal is 0, because on that month the product never sale.

Does someone have idea or suggestion or hint?
Thank you.

Lemune wrote:
Either use a month_table and outer join against that, i.e.

select p.name, MONTHNAME(s.date) as month,sum(CASE WHEN d.quantity is
NULL THEN 0 ELSE d.quantity END ) AS sumTotal from

lateral(values (1),(2),(3),(4),...,(12)) as mt(m)

left outer join ...


or add 0 for those month/product that does not exists, i.e.

<your query>
union
select ...., 0 from ...
where not exists (
<your query> + correlation clause's
)


/Lennart

Hi Lennart.
Thanks for your reply.

I have tried your suggestion, here my new query (I'm using mysql 5)
SELECT p.name,MONTHNAME(s.date) as month,sum(CASE WHEN d.quantity is
NULL THEN 0 ELSE d.quantity END ) AS sumTotal from products p left
outer join (sales s inner join sales_details d on s.id=d.sale_id) on
p.id=d.product_id WHERE S.date BETWEEN CAST('2007-01-01 00:00:00' as
Datetime) AND CAST('2007-07-30 23:59:59' as Date) group by p.id,month
Union
SELECT p.name, MONTHNAME( s.date ) AS MONTH , 0 AS sumTotal FROM
products p, sales s WHERE NOT EXISTS (SELECT p.name,
MONTHNAME( s.date ) AS MONTH , sum(CASE WHEN d.quantity IS NULL THEN 0
ELSE d.quantity END ) AS sumTotal FROM products p LEFT OUTER JOIN
( sales s INNER JOIN sales_details d ON s.id = d.sale_id ) ON p.id =
d.product_id WHERE S.date BETWEEN CAST( '2007-01-01 00:00:00' AS
Datetime ) AND CAST( '2007-07-30 23:59:59' AS Date ) GROUP BY p.id,
MONTH );

It still doesn't give me the 0 value for my question.
Where did i make mistake for my new query, because i understand that
WHERE NOT EXISTS will give me value 0 for condition where it give
nothing.

Lemune wrote:
Sorry for any confusion I might have caused. You still need to generate
the domain of all possible values. Either create a table and fill it
with all possible values, or construct one on the fly. I dont know how
to do this in mysql, but here are some examples I've seen in other dbms:

lateral(values ('January'),('February', etc ) ) month_names (x)

(select 'January' union Select 'February' union etc )) month_names (x)

If you create a permanent table, it is probably better to do it on date
basis rather than on a month basis

Though I don't know so much about MySQL, following might be worth to
try as far as I referenced "MySQL 5.0 Reference Manual".

But, I have two questions about your original query.
1) Can "sales RIGHT OUTER JOIN sales_detail" be replaced with INNER
JOIN? Because each row of sales_detail must have matching row of sales
in usual design of tables. Without matching sales row, how to know
sales date of a row in sales_detail?
2) Isn't it neccesary MAX or MIN for p.name in topmost SELECT list on
MySQL?

SELECT MAX(p.name) as product_name, MONTHNAME(m.date) as month
, SUM(COALESCE(d.quantity, 0)) AS sumTotal
FROM products p
INNER JOIN
(SELECT DATE_ADD(CAST('2007-01-01' as Date), INTERVAL mm
MONTHS)
from (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION
SELECT 9 UNION SELECT 10 UNION SELECT 11
) all_mm(mm)
WHERE mm+1 BETWEEN MONTH(CAST('2007-01-01' as Date))
AND MONTH(CAST('2007-07-30' as Date))
) m(date)
LEFT OUTER JOIN
(SELECT id, date
FROM sales
WHERE date BETWEEN CAST('2007-01-01' as Date)
AND CAST('2007-07-30' as Date)
) s
INNER JOIN
sales_details d
ON s.id=d.sale_id
ON p.id=d.product_id
AND MONTH(s.date) = MONTH(m.date)
GROUP BY p.id, MONTHNAME(m.date);