PHP MSSQL COUNT per Year

[Call-911]

Hello All,

 

I need to count the number of SQL date entries we have per year. I can coun this by:

 

SELECT COUNT(*) FROM news WHERE show='true' AND date >= '2011-07-01'

 

But how can I make it change years automatically. Our fiscal year starts July 1, and I'd like it to restart the count automatically every year. Does that make sense?

[Psycho]

You have a field where you put the string value of "true"? If the only value is true or false you should be using a tinyint type field and storing a 0 or 1.

 

SELECT COUNT(*)
FROM `news`
WHERE `show` = 'true'
  AND YEAR(`date`) = YEAR(NOW())

[PFMaBiSmAd]

For the year start/end -

SELECT COUNT(*) FROM news WHERE `show`='true' AND IF(CURDATE() >= CONCAT(YEAR(CURDATE()),'-07-01'),
date BETWEEN CONCAT(YEAR(CURDATE()),'-07-01') AND CONCAT(YEAR(CURDATE())+1,'-06-30'),
date BETWEEN CONCAT(YEAR(CURDATE())-1,'-07-01') AND CONCAT(YEAR(CURDATE()),'-06-30'))

[Psycho]

OK, I FAIL. I didn't catch that part about the fiscal year end in July (and I work for an accounting software company, LOL).