CODEWITHSUNDEEP
sqlsqlitedate
Onyeka Umeweni
Onyeka Umeweni

Reputation: 1

Trouble with Date Functions

I am a beginner with SQL, working on SQLite.

My query/subquery that counts # of values returns a null value or no value at all when I use between or >=/< alongside Dates. I don't think it is a syntax error, but maybe I have some kind of conflicting conditionals?

The expected result is the count of values that meet the conditions, and are between the days listed. The conditions also include an aggregate from between the selected days.

For reference, my Date values are inserted in the format DD/MM/YYYY but even if I exchange DD/MM/YYYY with YYYY/MM/DD or YYYY-MM-DD in the query, the issue remains. Edit: The Data Type for my Date values is "DATE", which might actually be an issue, since I think I might have made a syntax mistake naming the row the same as a function and a data type?

SELECT COUNT (*) as "Number of Games",
    CASE
        WHen ftp > .968077144 then "Signifigantly Above League Average"
        WHEN ftp > .884497663 then "Firmly Above League Average"
        WHEN ftp > .800918182 then "Slighty Above League Average"
        WHEN ftp > .717338701 then "Slightly Below League Average"
        WHen ftp > .63375922 then "Firmly Below League Average"
        when ftp > .565 then "Signifigantly Below League Average"
        Else "Hack A Simmons"
        End AS "Free Throw Percentage"
        from BStats
        Where GS = 1 and date BETWeen '17/10/2017' and '18/04/2018'
        GROUP BY "Free Throw Percentage"
        HAVING ast >= (SELECT avg(ast) from BStats where GS = 1 and date BETWEEN '17/10/2017' and '18/04/2018');

Revised Code: I changed the column name for the Dates into playdate instead to resolve any reserved names issues. I also switched to >= and >, from the between function. Should I change the data type for dates into something else? I'm still getting a null reply. Thanks for the help!

SELECT COUNT (*) as "Number of Games",
    CASE
        WHen ftp > .968077144 then "Signifigantly Above League Average"
        WHEN ftp > .884497663 then "Firmly Above League Average"
        WHEN ftp > .800918182 then "Slighty Above League Average"
        WHEN ftp > .717338701 then "Slightly Below League Average"
        WHen ftp > .63375922 then "Firmly Below League Average"
        when ftp > .565 then "Signifigantly Below League Average"
        Else "Hack A Simmons"
        End AS "Free Throw Percentage"
        from BStats
        Where GS = 1 and playdate >= '2017-10-17' and playdate < '2018-04-18'
        GROUP BY "Free Throw Percentage"
        HAVING ast >= (SELECT avg(ast) from BStats where GS = 1 and playdate >= '2017-10-17' and playdate < '2018-04-18');

Upvotes: 0

Views: 32

Answers (1)

Wonderland Alice
Wonderland Alice

Reputation: 55

It really depends on the datatype of your "date" column. Also, is the name of your column "date"? It is a reserved word in SQL, so that might be throwing something off as well. Try enclosing it in brackets if it gives you trouble.

Upvotes: 0

Related Questions