LiveCode Forums

I need to count the number of times a column in a table based upon the WHERE filter.
If possible I would like to count, sum or average multiple multiple columns.
I tried the following query structures but none of them give a result. The result is always 0, also no error.

// ========== Query types I tried ======
--------------------- simplest version
-- SELECT COUNT(tColumnName) FROM tTablename WHERE condition
--------------------
-- SELECT COUNT(ColumnName1) as 'tColumnName1', COUNT(ColumnName2) as 'tColumnName2', COUNT(ColumnName3) as 'tColumnName3' FROM tTablename WHERE condition
--------------------
-- SELECT SUM(CASE WHEN column1 IS NOT NULL THEN 1 ELSE 0 END) AS column1_count, SUM(CASE WHEN column2 IS NOT NULL THEN 1 ELSE 0 END) AS column2_count, SUM(CASE WHEN column3 IS NOT NULL THEN 1 ELSE 0 END) AS column3_count FROM tTablename WHERE condition
//============================

The line below shows e.g. the command query that goes into 'revExecuteSQL DatabaseID, tCmd' for the simplest query type I tried.
SELECT COUNT(L0_NodeLabel) FROM Registrations WHERE IssueType='%' AND Status='Done' AND L0_NodeLabel='%' AND IssueYN='%'

How to achieve my goals and what am I doing wrong?

Kind regards,

Paul

Dag Paul,

string concatenation is the magic word! Best

Klaus

Thanks Klaus,

used you quote and single quote function (thanks )
With this query : SELECT COUNT('L0_NodeLabel') FROM 'Registrations' WHERE IssueType='%' AND Status='Done' AND L0_NodeLabel='%' AND IssueYN='%'
the result is stil 0. I know there are records that should be counted.

Klaus with a K, verdomme!

I am not sure if the wildcard placeholder % can be used "solo" at all?
So why not leave out all these fields, if they do not matter*** and try something like this:
...
SELECT COUNT('L0_NodeLabel') FROM 'Registrations' WHERE Status='Done'

***I think you mean e.g. (WHERE IssueType='%') whatever value IssueType has, right?
And that means ANY db field named "IssueType"!


Best

Klaus

Will try it ... (Welke K? )

Simplified things to: SELECT COUNT('L0_NodeLabel') FROM 'Registrations' WHERE Status='Done'
Result is 0 and it should be 1.

I will keep on trying..

mrcoollion wrote: (Welke K? )

Pas op mijnheer!

That is strange indeed, good luck!

Have you ever used the SELECT COUNT query Klaus ?

Yes, but that was years ago and the SQL syntax is definitively correct!?

Does this give you a correct answer?
...
## No WHERE clause:
SELECT COUNT('L0_NodeLabel') FROM 'Registrations'
...
Or try without single quotes:
...
SELECT COUNT(L0_NodeLabel) FROM 'Registrations'
...

Hi Klaus,

Probably got it to work.
Tried all SQL options shown below and the last one (revDataFromQuery) gave me the expected result.
I do hope that this function also works with other Database types and not only SQLite because of the delimiters.

tCmd = SELECT COUNT('L0_NodeLabel') FROM 'Registrations' WHERE Status='Done'

-- revExecuteSQL DatabaseID, tCmd // No error but result is 0
-- put revdb_execute (DatabaseID, tCmd) into tTheResult /// No error but result is 0
-- put revQueryDatabase (DatabaseID, tCmd) into tTheResult // No error but result is 1132 which i cannot relate to the records (except there are three that should be counted)
put revDataFromQuery (tab,return,DatabaseID, tCmd) into tTheResult // Works, result is 3 which is correct !

I will keep on testing and post the result here.

Any comments, suggestions, idea's on what I am doing wrong or could do better please let me know.

Dictionnary is your friend.
is for INSERT, UPDATE queries etc.
, that's the way to go for a simple SELECT is for "cursor" (you probably don't need it, at least at the beginning).

Thx bangkok for the short and sweet explenations .....

For those who are interested.

Did some testing and changed query into : SELECT COUNT('ColumnName1') as 'tColumnName1', COUNT('ColumnName2') as 'tColumnName2', COUNT('ColumnName3') as 'tColumnName3' FROM 'tTablename' WHERE condition
Exact query command

SELECT COUNT('L0_NodeLabel') as 'L0_NodeLabel', COUNT('L1_NodeLabel') as 'L1_NodeLabel' FROM 'Registrations' WHERE Status='Done'

Just to try and see if i could count 2 columns separate and it works to my surprise.
The outcome (tTheResult) is a tab delimited variable (hoped it would be an array but no such luck).
With a repeat statement I placed the data into an Array with the Column name as identifier and this works fine for me.

Knowing this it would also work for SUM and AVG (average) calculations. Only need to change the COUNT for one of those and make sure that there always is a number available for SUM and AVG.

To catch a possible error i used the following code Thanks for all the help!