Help ExecuteSQL Newbie

[John Kostenbader]

I'm relatively new to the ExecuteSQL statement but I want to learn more about it in order to create a dashboard of charts without having to worry about the underlying layout's base table. I have a simple projects table that contains ID_Project and a field called "Project Category". I'd like to create a pie chart that represents a count of each category but I can't even get the base select statement correct. Here's what I've done already

ExecuteSQL ("SELECT ID_Projects, \"Project Category\"
FROM Projects GROUP BY \"Project Category\"" ; "" ; "" )

All I get is the dreaded "?"

I've tried a million variations of this and still cannot get it. Once I figure out the base select statement the challenge remains to count them. Any assistance would be really appreciated. Also does there exist a good reference for this function with lots of examples (I learn best from the examples)

Regards

John

[Kevin Frank]

Hi John, 

When you use GROUP BY, there must be an aggregate function such as "SUM" in the SELECT portion ... for example, this works:

ExecuteSQL ( "

SELECT idLedger, SUM ( amount )  
FROM Ledgerline
GROUP BY idLedger 

" ; "" ; "" )

...but this does not:

ExecuteSQL ( "

SELECT idLedger, amount 
FROM Ledgerline
GROUP BY idLedger 

" ; "" ; "" )

Hope this helps,

Kevin

Edited January 10, 2016 by Kevin Frank

[John Kostenbader]

It did help...Ultimately this is what I came up with which works in the Data Viewer:

ExecuteSQL(

"SELECT COUNT(ID_Projects), Project_Category
FROM Projects
GROUP BY Project_Category
 ";"   ";"")

In the data viewer I get the results I expect however, when I plug this into the "slice data" of a pie chart, I get nada. I have changed the data source to (current record - delimited). Not sure what else I'm doing wrong at this point. I can also see the value (now) of not adding spaces to your field names. Kind of a pain to deal with in this context

John

[Kevin Frank]

Hi John,

There are a couple problems here. 

#1. I may have inadvertently confused things with my examples. I don't like quotation marks getting in the way, so I put the opening quote on the first line, and all others on the bottom line. The semicolons are not surrounded by quotation marks as implied by your most recent example, they are delimiters separating pairs of empty quotes. I mention this because your most recent example is using three spaces as the column delimiter, and I'm guessing that's not actually what you want. But this leads us to the next issue.

#2. A pie chart expects a list of numbers, with one value per row. My example above, which returns two values per row, was only intended to demonstrate a valid use of GROUP BY.

For your pie chart, you need to come up with two separate lists... one for the the labels, and another for the data. If you want to stick with a single SQL statement, you could use a custom function to parse the two lists, e.g., Michael Horak's GetColumn (https://www.briandunning.com/cf/877), or you could use two separate SQL statements, e.g, 

LABELS

ExecuteSQL(

"SELECT DISTINCT ( Project_Category )
FROM Projects"

;"";"")

DATA

ExecuteSQL(

"SELECT COUNT ( ID_Projects )
FROM Projects
GROUP BY Project_Category"

;"";"")

Note: the two lists will both be sorted by Project_Category, ensuring a one-to-one correspondence between rows in the Label list and rows in the Data list.

Hope this helps,

Kevin

[John Kostenbader]

Extremely helpful Kevin...thanks so much for taking the time to answer this question.

[Kevin Frank]

You're welcome John.