At 13:53 +0000 1/2/03, Steve Vernon wrote:
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.
COUNT(*) counts rows, not values. Try using COUNT(projects.startYear)
instead, which will count only non-NULL values. (The LEFT JOIN returns
a row with all projects columns set to NULL for the case where there is
no project for a year.)
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
