Having SQL COUNT query problems

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Having SQL COUNT query problems

Postby 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
mrcoollion
 
Posts: 237
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Having SQL COUNT query problems

Postby 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
Klaus
 
Posts: 9488
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany

Re: Having SQL COUNT query problems

Postby 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.
mrcoollion
 
Posts: 237
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Having SQL COUNT query problems

Postby 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
Klaus
 
Posts: 9488
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany

Re: Having SQL COUNT query problems

Postby mrcoollion » Tue Jul 11, 2017 6:39 pm

Will try it ... (Welke K? :wink: )
mrcoollion
 
Posts: 237
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Having SQL COUNT query problems

Postby 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.
mrcoollion
 
Posts: 237
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Having SQL COUNT query problems

Postby Klaus » Tue Jul 11, 2017 6:57 pm

mrcoollion wrote: (Welke K? :wink: )

Pas op mijnheer! :D

That is strange indeed, good luck!
Klaus
 
Posts: 9488
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany

Re: Having SQL COUNT query problems

Postby mrcoollion » Tue Jul 11, 2017 6:59 pm

Have you ever used the SELECT COUNT query Klaus ?
mrcoollion
 
Posts: 237
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Having SQL COUNT query problems

Postby 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'
...
Klaus
 
Posts: 9488
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany

Re: Having SQL COUNT query problems

Postby 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.
mrcoollion
 
Posts: 237
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Having SQL COUNT query problems

Postby 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).
bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
 
Posts: 771
Joined: Fri Aug 15, 2008 7:15 am

Re: Having SQL COUNT query problems

Postby mrcoollion » Wed Jul 12, 2017 7:57 am

Thx bangkok for the short and sweet explenations ..... :D
mrcoollion
 
Posts: 237
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands

Re: Having SQL COUNT query problems

Postby 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
mrcoollion
 
Posts: 237
Joined: Thu Sep 11, 2014 1:49 pm
Location: The Netherlands


Return to Databases

Who is online

Users browsing this forum: No registered users and 1 guest

cron