Professional Web Applications Themes

Help on query for report - MySQL

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 ...

  1. #1

    Default Help on query for report

    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 Guest

  2. #2

    Default Re: Help on query for report

    Lemune wrote: 

    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
    J.O. Guest

  3. #3

    Default Re: Help on query for report

    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 Guest

  4. #4

    Default Re: Help on query for report

    Lemune wrote: 
    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
    Adam Guest

  5. #5

    Default Re: Help on query for report

    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.

    Lemune Guest

  6. #6

    Default Re: Help on query for report

    On 9 Jul, 07:41, Lemune <com> wrote: 

    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.

    Captain Guest

  7. #7

    Default Re: Help on query for report

    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.

    Lemune Guest

Similar Threads

  1. Crystal Report Query
    By Javagene in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 6th, 01:11 AM
  2. Report - Query & Display help - How to get totals?
    By JethroTull in forum Macromedia ColdFusion
    Replies: 7
    Last Post: July 8th, 03:18 PM
  3. Generating a Report - Help with the Query
    By JethroTull in forum Macromedia ColdFusion
    Replies: 10
    Last Post: May 5th, 08:29 PM
  4. SQL Query in Report Builder
    By SarojSrini in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 21st, 05:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139