Sounds to me like you'll need to join for that -- and list all the years you are interested in in another table. Try this?
SELECT y.Year, count(*) FROM Year as y LEFT JOIN projects ON project.startyear<=y.Year AND project.endyear>=y.Year GROUP BY y.Year; This will give you a count of 0 for any years without a project (Almost 100% certain =] ). But don't blame me if the query is grossly inefficient! Nick Elliott ----- Original Message ----- From: "Steve Vernon" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, December 31, 2002 10:44 AM Subject: Re: Year Lists > Martin, > Thanks for the reply. > > Good plan but at the moment my query is like (had to alter it slightly > as it was in PHP and multiple lines, so dont take it as working). > > SELECT projects.name, project.endyear, project.startyear FROM projects WHERE > projects.startyear <= '(INPUT THE YEAR HERE) && (projects.leaveyear >= > '(YEAR HERE AS WELL)' || projects.leaveyear = '0000' ) LIMIT 5000 > > What you suggest basically will give a count on start year (or end year > if altered), which I have implemented and working for ended year. Say a > project started in 1920 and another in 1921 and they both finish in 1925. I > would expect to get this: > > 1920 1 Project > 1921 2 Projects > 1922 2 Projects > 1923 2 Projects > 1924 2 Projects > 1925 2 Projects > 1926 0 Projects (or no line at all ideally). > .... up to 2003 > > > > Ideally if a year does not have any projects it would not be listed. > > Any more help would be great, or I could be wrong and you could have the > answer! > > Steve > > > > > On Tue, 2002-12-31 at 14:53, Steve Vernon wrote: > > > Hiya, > > > I have a database about projects in a company, they all have a start > > > year and end year. If the projects have not ended then they get a end > year > > > of 0000. I have made a SQL command, given a year, works out with > projects > > > are running in that year. > > > > > > The company I am doing this for, wants basically a drop down box > which > > > says (1950- 8 Projects) and such like. Now with the current command I > have > > > it would mean 92or so SQL commands as the company has records back to > 1910. > > > > > > Is there a way to do this in one command? I have searched all the > > > mannual and I can work out something similair to a for loop in SQL. > Would > > > variables help? > > > > > > > Hi Steve, > > > > You want to use "select .... group by", something like: > > > > select start_year, count(*) from projects group by start_year; > > > > > > == > > Martin > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
