Between not returning expected results

[tjverge]

Hi,

I have a SQL Query of

SELECT sum('chiralstuctors') FROM `pi` WHERE `pilot` = 'Test' AND 'pidate' BETWEEN '2011-2-1' AND '2011-2-7'

it should return 3000 but it keeps coming back Null, any ideas how to get the right data to return?

[Pikachu2000]

Try BETWEEN '2011-02-01' AND '2011-02-07'

[tjverge]

thanks for the idea but it still came back NULL

[Pikachu2000]

Change the single quotes around pidate to `backticks` . . .

[tjverge]

changed, still same result I tried with both 2011-1-2 and 2011-01-02

[Pikachu2000]

What is the data type of the pidate field?

[tjverge]

date

[Pikachu2000]

Have you tried executing the query in phpMyAdmin to see what it returns? I don't see any reason it shouldn't work unless there are no matching records. Post the code you're using to run the query and display the results.

[tjverge]

SELECT sum('chiralstuctors') FROM `pi` WHERE `pilot` = 'Test' AND `pidate` BETWEEN '2011-01-02' AND '2011-07-02'

 

Results:0

 

But in my table I have:3000 under column  chiralstuctors

 

[Pikachu2000]

What is the data type of the chiralstructors field, and are you trying to get a count of the number of records returned?

[tjverge]

data type is bigint, and no all I did was run the query in phpmyadmin, I'm just looking for a sum of all the numbers in that column.

[tjverge]

I put together a php code for it and it finds 1 record that way.

$sql = "SELECT sum('chiralstuctors') FROM `pi` WHERE `pilot` = 'Test' AND `pidate` BETWEEN '2011-2-1' AND '2011-2-7'";
$results= mysql_query($sql) or die(mysql_error());
$numrows = mysql_num_rows($results);
Echo $numrows. "Records found";

 

Is that what you meant by finding  the number of records returned?

[Pikachu2000]

I was just making sure you weren't mistakenly using SUM() when you intended to use COUNT(); that happens fairly frequently.

 

So we're on the same page here, The query executes without error in phpMyAdmin, and returns a value of 0 for sum(`chiralstructors`), correct? How many results does the query show it returns if you change it to read

 

SELECT `chiralstructors` FROM `pi` . . .  instead of using the SUM function?

[tjverge]

It's solved thanks for you help you had the right idea with changing pidate to use ` chiralstuctors needed them too, thank you again for working with me on this

[Pikachu2000]

Ah, hell. I didn't even notice that one. Getting old, eyes going bad. That's my story and I'm sticking to it.