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" <steve@stripped>
>> To: <mysql@stripped>
>> 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
