AnsweredAssumed Answered

SQL Chart Group, return single column

Question asked by jtorpy on Feb 28, 2018
Latest reply on Mar 1, 2018 by jtorpy

I have this as he y-axis data for a chart. (x-axis is a select distinct on YrMon) I need to return one set of data but first I need to group by YrMon. I guess FM requires group by fields to be in the select argument. So that means the sql returns two columns rather one. So I'm trying to do a subsequent sql to take that two column SQL and return only one.

 

I also want that one column summed across the YrMon.

 

I could create a whole chart into a variable with a separate script, but I think it's possible to just do in the chart data field. It doesn't seem to be working. Maybe I could just sum in the first sql and then return _firstSQL(2) or something... not sure.

 

Everyone has been really great here. I appreciate the help. Hopefully I'll get good enough to help others one of these days.

 

Let ([_Row = 1;

_Item = GetValue (tblGlobals::g_Metric ; _Row );

_firstSQL = ExecuteSQL("

SELECT YrMon, Quantity

FROM impDataRpt

WHERE Metrics = ?

AND (\"Year\" >=? AND Mo >=?)

AND (\"Year\" <=? AND Mo <=?)

GROUP BY YrMon

ORDER BY \"Year\", Mo";

"";"";

_Item ; tblGlobals::g_YrStart; tblGlobals::g_MoStart;tblGlobals::g_YrEnd;tblGlobals::g_MoEnd)

];

 

ExecuteSQL("

SELECT Sum(Quantity)

GROUP BY YrMon

FROM _firstSQL";"";""

)

Outcomes