Thanks for the email...
Brilliant idea, but it does not give 0 for rows without a project for a
year. It gives 1 for these rows.
As a test idea, I removed the group by and count, and instead of count
outputted the start and end year.
What that gave me was say for 1953 where there was 3 projects, 3 lines with
the start and end year for those projects. But for a year without a project
gives start and end year as null.
e.g. (not true examle)
year startyear endyear
1901 NULL NULL
....
1953 1952 1954
1953 1951 1956
1953 1950 1980
....
I suppose I need to do a where to remove null values, but I cant work
out where it goes!
Thanks, ;-)
Steve
XX
> 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
>
---------------------------------------------------------------------
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
