List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 2 2003 6:58pm
Subject:Re: Year Lists
View as plain text  
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

Thread
Year ListsSteve Vernon31 Dec
  • Re: Year ListsMartin Waite31 Dec
  • Re: Year ListsSteve Vernon31 Dec
Re: Year ListsNicholas Elliott31 Dec
  • Re: Year ListsSteve Vernon2 Jan
    • Re: Year ListsPaul DuBois2 Jan
  • Re: Year ListsNicholas Elliott2 Jan