It worked. Thanks a lot asgallant.
please find attached snapshots, I want the graph to be grouped under the
year.
On Thursday, March 20, 2014 1:28:01 PM UTC-6, asgallant wrote:
>
> That JSON string is malformed. There are extra "]" showing up after
> columns that don't belong, and you are missing a comma after the last
> column, before "rows". I will highlight them for you ( is extra, is
> missing):
>
> {"cols": [ {id:"", label:"ADIY", type:"string"}, {id:"",
> label:"FUNDS_CNT", type:"number"}], {id:"", label:"FUNDS_NOM_CNT",
> type:"number"}], {id:"", label:"FUNDS_FEED_CNT", type:"number"}], {id:"",
> label:"SEL_CNT", type:"number"}], {id:"", label:"SEL_AWARD",
> type:"number"}], {id:"", label:"FED_AWARD", type:"number"}], "rows": [
> {"c":[{v: "1314" }, {"v": "5"}, {"v": "5"}, {"v": "4"}, {"v": "54"}, {"v":
> "42133"}, {"v": "35375"}]}]}
>
> Fix those and it works. If you follow my PHP example, you shouldn't run
> into this problem.
>
> On Thursday, March 20, 2014 2:57:50 PM UTC-4, [email protected] wrote:
>>
>> Sorry to bug you,
>>
>> This is my output of JSON table
>>
>> {"cols": [ {id:"", label:"ADIY", type:"string"}, {id:"",
>> label:"FUNDS_CNT", type:"number"}], {id:"", label:"FUNDS_NOM_CNT",
>> type:"number"}], {id:"", label:"FUNDS_FEED_CNT", type:"number"}], {id:"",
>> label:"SEL_CNT", type:"number"}], {id:"", label:"SEL_AWARD",
>> type:"number"}], {id:"", label:"FED_AWARD", type:"number"}] "rows": [
>> {"c":[{v: "1314" }, {"v": "5"}, {"v": "5"}, {"v": "4"}, {"v": "54"}, {"v":
>> "42133"}, {"v": "35375"}]}]}
>>
>> Did I make any mistake? I do not see any chart as an output.
>>
>> On Tuesday, March 18, 2014 1:38:58 PM UTC-6, asgallant wrote:
>>>
>>> The PHP code I posted above shows the basics of how to create the
>>> DataTable in PHP. Other than that, there isn't much (if anything) you need
>>> to do in PHP to make a chart - the rest is all in javascript.
>>>
>>> On Monday, March 17, 2014 7:28:28 PM UTC-4, [email protected] wrote:
>>>>
>>>> Thanks a ton asgallant, Could you please share any sample php (input
>>>> from SQL as array format)program for column or bar chart?
>>>>
>>>>
>>>> On Monday, March 17, 2014 4:36:11 PM UTC-6, asgallant wrote:
>>>>>
>>>>> You would have to make that a subquery, like this:
>>>>>
>>>>> $CountQuery = "SELECT
>>>>> Year,
>>>>> SUM(CASE WHEN Month = '01' THEN Count ELSE 0 END) as Jan,
>>>>> SUM(CASE WHEN Month = '02' THEN Count ELSE 0 END) as Feb,
>>>>> SUM(CASE WHEN Month = '03' THEN Count ELSE 0 END) as Mar,
>>>>> SUM(CASE WHEN Month = '10' THEN Count ELSE 0 END) as Oct,
>>>>> SUM(CASE WHEN Month = '11' THEN Count ELSE 0 END) as Nov,
>>>>> SUM(CASE WHEN Month = '12' THEN Count ELSE 0 END) as Dec
>>>>> FROM (
>>>>> SELECT Year_Sd as Year...rest of your query in here...
>>>>> ) a
>>>>> GROUP BY Year
>>>>> ORDER BY Year";
>>>>>
>>>>> There may be a more efficient way of handling that, but I'm not
>>>>> certain off the top of my head how you would handle the problems that
>>>>> COUNT
>>>>> DISTINCT brings up in conjunction with the case statements, so I would
>>>>> try
>>>>> this first and see if runs OK.
>>>>>
>>>>> On Monday, March 17, 2014 6:20:27 PM UTC-4, [email protected] wrote:
>>>>>>
>>>>>> Thank you asgallant.
>>>>>>
>>>>>> But my query look like this, I am not sure whether I can use SUM on
>>>>>> top of Count. Could you please help?
>>>>>>
>>>>>> Select
>>>>>>
>>>>>> Year_Sd as Year,
>>>>>>
>>>>>> TO_CHAR(Date_sd,'MM') as Month,
>>>>>>
>>>>>> count(distinct user_id) as Count
>>>>>>
>>>>>> from student_sd
>>>>>>
>>>>>> where active)ind = 'A'
>>>>>>
>>>>>> AND ((Year_Sd = '1314'
>>>>>>
>>>>>> and Date_sd < ('09-MAR-2013'))
>>>>>>
>>>>>> OR (Year_Sd = '1213'
>>>>>>
>>>>>> and Date_sd < ('05-MAR-2012'))
>>>>>>
>>>>>> OR (Year_Sd = '1112'
>>>>>>
>>>>>> and Date_sd < ('05-MAR-2011'))
>>>>>>
>>>>>> OR (Year_Sd = '1011'
>>>>>>
>>>>>> and Date_sd < ('05-MAR-2010'))
>>>>>>
>>>>>> OR (Year_Sd = '1415'
>>>>>>
>>>>>> and Date_sd < ('05-MAR-2014')))
>>>>>>
>>>>>> group by TO_CHAR(Date_sd,'MM'),Year_Sd
>>>>>> order by Year_Sd
>>>>>>
>>>>>> On Monday, March 17, 2014 4:08:30 PM UTC-6, asgallant wrote:
>>>>>>>
>>>>>>> First, SQL does not guarantee that your data is returned in any
>>>>>>> particular order, so you can't count on getting data back in the order
>>>>>>> you
>>>>>>> expect it unless you specifically order it in the query. Second, what
>>>>>>> you
>>>>>>> are trying to do in PHP is quite unwieldy; it is much easier to make
>>>>>>> SQL do
>>>>>>> the heavy lifting for you. Try this instead:
>>>>>>>
>>>>>>> $CountQuery = "SELECT
>>>>>>> Year,
>>>>>>> SUM(CASE WHEN Month = '01' THEN Count ELSE 0 END) as Jan,
>>>>>>> SUM(CASE WHEN Month = '02' THEN Count ELSE 0 END) as Feb,
>>>>>>> SUM(CASE WHEN Month = '03' THEN Count ELSE 0 END) as Mar,
>>>>>>> SUM(CASE WHEN Month = '10' THEN Count ELSE 0 END) as Oct,
>>>>>>> SUM(CASE WHEN Month = '11' THEN Count ELSE 0 END) as Nov,
>>>>>>> SUM(CASE WHEN Month = '12' THEN Count ELSE 0 END) as Dec
>>>>>>> FROM table
>>>>>>> GROUP BY Year
>>>>>>> ORDER BY Year";
>>>>>>>
>>>>>>> Your PHP then becomes vastly simplified:
>>>>>>>
>>>>>>> $table = array(
>>>>>>> 'cols' => array(
>>>>>>> // Labels for your chart, these represent the column titles
>>>>>>> array('label' => 'AidYear', 'type' =>'string'),
>>>>>>> array('label' => 'Jan', 'type' => 'number'),
>>>>>>> array('label' => 'Feb', 'type' => 'number'),
>>>>>>> array('label' => 'Mar', 'type' => 'number'),
>>>>>>> array('label' => 'Oct', 'type' => 'number'),
>>>>>>> array('label' => 'Nov', 'type' => 'number'),
>>>>>>> array('label' => 'Dec', 'type' => 'number')
>>>>>>> ).
>>>>>>> 'rows' => array()
>>>>>>> );
>>>>>>> while($r = oci_fetch_array($CountQuery)) {
>>>>>>> $table['rows'][] = array('c' => array(
>>>>>>> array('v' => $r['Year']),
>>>>>>> array('v' => $r['Jan']),
>>>>>>> array('v' => $r['Feb']),
>>>>>>> array('v' => $r['Mar']),
>>>>>>> array('v' => $r['Oct']),
>>>>>>> array('v' => $r['Nov']),
>>>>>>> array('v' => $r['Dec'])
>>>>>>> ));
>>>>>>> }
>>>>>>> echo json_encode($table, JSON_NUMERIC_CHECK);
>>>>>>>
>>>>>>> On Monday, March 17, 2014 5:28:57 PM UTC-4, [email protected]:
>>>>>>>>
>>>>>>>>
>>>>>>>> Please find attached for sample chart of what i am trying achieve.
>>>>>>>>>
>>>>>>>>
>>>>>>>> Thanks in advance.
>>>>>>>>
>>>>>>>
--
You received this message because you are subscribed to the Google Groups
"Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.
<<attachment: actual.gif>>
<<attachment: expected.gif>>
