I have the following sql code (it works in terminal) that is too count the number of distinct clients in a state, then count the total number of clients per state.
CREATE TEMPORARY TABLE statecount (select DISTINCT(TAX_ID), FIRST_NAME, LAST_NAME, STATE from CLIENTS WHERE ACTIVE = 'TRUE')
select STATE, count(STATE) from statecount group by STATE
I want to create a report that shows the state, and the number of clients in that state (note that some clients have more than one account, that is why I need to only count that client once).
Is there a way to do this in ART?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
But you have to define - to which state should the client count? Just the "first" one, by which sort? This is what you have to program in your SQL-query...
And if you need more than one statement to prepare date for your report, you can define the no# of the statement, of which the output should be used (see "Display Resultset")
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have the following sql code (it works in terminal) that is too count the number of distinct clients in a state, then count the total number of clients per state.
CREATE TEMPORARY TABLE statecount (select DISTINCT(TAX_ID), FIRST_NAME, LAST_NAME, STATE from CLIENTS WHERE ACTIVE = 'TRUE')
select STATE, count(STATE) from statecount group by STATE
I want to create a report that shows the state, and the number of clients in that state (note that some clients have more than one account, that is why I need to only count that client once).
Is there a way to do this in ART?
everything you can do in SQL you can do in ART.
But you have to define - to which state should the client count? Just the "first" one, by which sort? This is what you have to program in your SQL-query...
And if you need more than one statement to prepare date for your report, you can define the no# of the statement, of which the output should be used (see "Display Resultset")
Thanks Gerd... I did not know I need to add "?allowMultiQueries=true" in my data source url. That made it work!
ah - ok, this may depend on your database system. Which system are you using?
For MSSQL and IBM, i didn't have to do this.