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] wrote:
>>
>>
>> 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.
