Having SQL COUNT query problems

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
mrcoollion
Posts: 709
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Having SQL COUNT query problems

Post by mrcoollion » Tue Jul 11, 2017 3:36 pm

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

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Having SQL COUNT query problems

Post by Klaus » Tue Jul 11, 2017 4:01 pm

Dag Paul,

string concatenation is the magic word!

Code: Select all

...
put "SELECT COUNT(" & q2(tColumnName) & ") FROM" && q2(tTablename) & " WHERE condition...") into tSQL
...

## QUOTE a string
function q tString
  return QUOTE & tString & QUOTE
end q

## SINGLE quote a string
function q2
  return "'" & tString & "'"
end q2
Best

Klaus

mrcoollion
Posts: 709
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Having SQL COUNT query problems

Post by mrcoollion » Tue Jul 11, 2017 4:37 pm

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.

:?
Last edited by mrcoollion on Tue Jul 11, 2017 6:40 pm, edited 2 times in total.

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Having SQL COUNT query problems

Post by Klaus » Tue Jul 11, 2017 4:44 pm

Klaus with a K, verdomme! 8)

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

mrcoollion
Posts: 709
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Having SQL COUNT query problems

Post by mrcoollion » Tue Jul 11, 2017 6:39 pm

Will try it ... (Welke K? :wink: )

mrcoollion
Posts: 709
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Having SQL COUNT query problems

Post by mrcoollion » Tue Jul 11, 2017 6:51 pm

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

I will keep on trying..
Last edited by mrcoollion on Tue Jul 11, 2017 6:58 pm, edited 1 time in total.

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Having SQL COUNT query problems

Post by Klaus » Tue Jul 11, 2017 6:57 pm

mrcoollion wrote: (Welke K? :wink: )
Pas op mijnheer! :D

That is strange indeed, good luck!

mrcoollion
Posts: 709
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Having SQL COUNT query problems

Post by mrcoollion » Tue Jul 11, 2017 6:59 pm

Have you ever used the SELECT COUNT query Klaus ?

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Having SQL COUNT query problems

Post by Klaus » Tue Jul 11, 2017 7:10 pm

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

mrcoollion
Posts: 709
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Having SQL COUNT query problems

Post by mrcoollion » Tue Jul 11, 2017 7:43 pm

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.

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: Having SQL COUNT query problems

Post by bangkok » Tue Jul 11, 2017 8:15 pm

Dictionnary is your friend.

Code: Select all

revExecuteSQL
is for INSERT, UPDATE queries etc.

Code: Select all

revDataFromQuery
, that's the way to go for a simple SELECT

Code: Select all

revQueryDatabase
is for "cursor" (you probably don't need it, at least at the beginning).

mrcoollion
Posts: 709
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Having SQL COUNT query problems

Post by mrcoollion » Wed Jul 12, 2017 7:57 am

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

mrcoollion
Posts: 709
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Having SQL COUNT query problems

Post by mrcoollion » Wed Jul 12, 2017 3:29 pm

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 :D 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

Code: Select all

put the result into tRes // Or use the 'tTheResult' variable
if tRes contains "Error" 
then
-- Do what needs to be done
else
-- Do what needs to be done
end if
Thanks for all the help! :D

Post Reply

Return to “Databases”