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