Android Question Database query

[anaylor01]

How can I get this to return a value?

SELEct  ifnull(COUNT(*),'0') FROM  GamePlay GROUP BY   teamscore, teamround HAVING    COUNT(*) > 1

 

[Erel]

You should use SQL.ExecQuery or ExecQuerySingleResult.

 

[anaylor01]

I am.

If  SQL1.ExecQuerySingleResult("SELEct  ifnull(COUNT(*),'0') FROM  GamePlay GROUP BY  teamscore, teamround HAVING  COUNT(*) > 1") > 1 Then)

 

[Erel]

And what happens when you run it?

 

[anaylor01]

It returns nothing. I was expecting it to return a zero.

 

[Erel]

What are you trying to calculate?

 

[anaylor01]

I am just trying to see if there is a duplicate. But I need it to return a value. Right now it returns nothing. Not a null or a zero.

 

[Mahares]

I am just trying to see if there is a duplicate. But I need it to return a value. Right now it returns nothing. Not a null or a zero.

I do not think you can get a single value using an aggregate query where you are grouping by two columns. I think the query returns a cursor like this, although I stand corrected:

Dim MyQuery As String = "Select  ifnull(COUNT(*),'0') AS MyCount FROM  GamePlay GROUP BY  teamscore, teamround HAVING  COUNT(*) > 1"   
    curs=SQL1.ExecQuery(MyQuery)
    For i=0 To curs.RowCount-1
        curs.Position=i
        Log($"my count is:  ${curs.Getint("MyCount")}"$ )
    Next
    curs.Close

I tested the concept on one of my databases, with different columns of course and I was able to view results in the cursor.

 

[anaylor01]

Yes it does if there are duplicates. But it doesn't if there aren't any duplicates and I need it to if it doesn't have duplicates. Or should I say it shouldn't error out if there aren't any duplicates.

 

[LucaMs]

I tested the concept on one of my databases, with different columns of course and I was able to view results in the cursor

Yes it does if there are duplicates. But it doesn't if there aren't any duplicates and I need it to if it doesn't have duplicates. Or should I say it shouldn't error out if there aren't any duplicates.

Dim ThereAreDuplicates As Boolean

Dim MyQuery AsString = "Select COUNT(*) AS MyCount FROM GamePlay GROUP BY teamscore, teamround HAVING MyCount > 1"
curs = SQL1.ExecQuery(MyQuery)
ThereAreDuplicates = (curs.RowCount > 0)
curs.Close

IfNull seems not working when you use Count (it works well using Sum, for example )

 

[aeric]

Sub CountDuplicate() As Int
    Dim qry As String
    Dim cur As Cursor
    Try
    qry = "SELECT COUNT(*) AS duplicate_count FROM GamePlay GROUP BY teamscore, teamround HAVING duplicate_count > 1"
    cur = SQL.ExecQuery(qry)
    Return cur.RowCount  
    Catch
        Log("Error: " & LastException.Message)
        Return 0
    End Try
End Sub

 

[Mahares]

Log("Error: " & LastException.Message)
Return 0

I do not think you need the catch block because there is no error when the count is 0. The code can be simply as follows:

Sub CountDuplicate() As Int
    Dim qry As String
    Dim cur As Cursor
    qry = "SELECT COUNT(*) AS duplicate_count FROM GamePlay GROUP BY teamscore, teamround HAVING duplicate_count > 1"
    cur = SQL.ExecQuery(qry)
    Return cur.RowCount  
End Sub

If no records, log(CountDuplicate) will display 0

 

[aeric]

I do not think you need the catch block because there is no error when the count is 0. The code can be simply as follows:

Sub CountDuplicate() As Int
    Dim qry As String
    Dim cur As Cursor
    qry = "SELECT COUNT(*) AS duplicate_count FROM GamePlay GROUP BY teamscore, teamround HAVING duplicate_count > 1"
    cur = SQL.ExecQuery(qry)
    Return cur.RowCount 
End Sub

If no records, log(CountDuplicate) will display 0

Just in case. It is up to developer practice.

 

[LucaMs]

... but... it's enough?

Now YOU should write a function like:

Public Sub GetDuplicatesPKs(TableName As String, Fields As List, PKField As String) As List

(hoping to have a single field as Primary Key, otherwise... well, this is your task )