Hello all.
I have this difficulty with my query for my report.
I want to create report that use bar chart, for the data I'm using
query to get it.
This is my query :
SELECT p.name,MONTHNAME(s.date) as month,if(sum(d.quantity) IS Null,
0,sum(d.quantity)) as sumQuantity from products p inner 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 MONTHNAME(s.date),p.id;

The problem is when there is no record on that month for the product i
want it to show 0 value, so the chart will show 0 to, not nothing.
Any clue or help?
Thanks.

Lemune wrote:
> Hello all.
> I have this difficulty with my query for my report.
> I want to create report that use bar chart, for the data I'm using
> query to get it.
> This is my query :
> SELECT p.name,MONTHNAME(s.date) as month,if(sum(d.quantity) IS Null,
> 0,sum(d.quantity)) as sumQuantity from products p inner 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 MONTHNAME(s.date),p.id;
>
> The problem is when there is no record on that month for the product i
> want it to show 0 value, so the chart will show 0 to, not nothing.
> Any clue or help?
> Thanks.
>

Think you need to make a function/procedure that returns this value for you,
or you do the checking in the script language that you use

simple php example

if(mysql_num_rows()) {
while($row=mysql_fetch_array($res)) {
echo $row['name'] ." ";
echo $row['month'] ." ";
echo $row['sumQuantity'] ."\n";
}
} else {
echo "0";
}

--

//Aho

Thank you for your reply Aho.
In simple, what i'm trying to get with my query is the query will show
record from month 1-7 (Jan,Feb, and so on) for each product, include
the month that the product didn't have record on the sales_detail
record on the month from sales. In other word if i have 5 product, i
will have 35 record.

In your example, it will check whether it has row or not, if not then
give value 0.

Lemune wrote:
> Thank you for your reply Aho.
> In simple, what i'm trying to get with my query is the query will show
> record from month 1-7 (Jan,Feb, and so on) for each product, include
> the month that the product didn't have record on the sales_detail
> record on the month from sales. In other word if i have 5 product, i
> will have 35 record.
>
> In your example, it will check whether it has row or not, if not then
> give value 0.
>
>
I think the problem lies with your inner joins. It will likely not show
products without sales. An easy way to check is to remove the summary
calculations and see if your query returns the product data needed to
make the query.

You could also try using a nested query, they tend to work well in
reporting and my give you better performance. It always depends on the
table structures and sizes.

Adam

Hi, Adam.
Thanks for your reply.

I tried to use outer join like your suggestion. This is my query:
( remove the sales, i tried just products and sales_details)
SELECT p.name, sum(CASE WHEN d.quantity is NULL THEN 0 ELSE d.quantity
END ) AS sumTotal FROM products p Left OUTER JOIN sales_details d ON
p.id = d.product_id group by p.id;

and this query show all products.

Then i use 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 still not show 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.

On 9 Jul, 07:41, Lemune <alfredosilito...@gmail.com> wrote:
> Hi, Adam.
> Thanks for your reply.
>
> I tried to use outer join like your suggestion. This is my query:
> ( remove the sales, i tried just products and sales_details)
> SELECT p.name, sum(CASE WHEN d.quantity is NULL THEN 0 ELSE d.quantity
> END ) AS sumTotal FROM products p Left OUTER JOIN sales_details d ON
> p.id = d.product_id group by p.id;
>
> and this query show all products.
>
> Then i use 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 still not show 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.

You need to create a calendar table and have that as the main table,
using LEFT JOINS against the other tables. Then you get an entry for
each date in the calendar table.

Thanks Captain Paralytic, for your suggestion.
I'm for the meantime will use some function that will create and array
that suit my need, where the array will contain the query result, and
then my report datasource will use that array, untill I found another
solution that work.

Thanks all.