[Babase] Re: query from Marie

Karl O. Pinc kop at meme.com
Thu May 10 15:12:05 EDT 2007


No reason not to use the list for this so everybody
can see....

On 05/10/2007 01:48:22 PM, Lacey Maryott wrote:
> select count(cycpoints.cid), members.grp,
> date_part('month', members.date) as month,
> date_part('year', members.date) as year from
> cycpoints, cycles, members where members.sname =
> cycles.sname and cycles.cid = cycpoints.cid and
> cycpoints.code='D' group by members.grp, members.date;
> 
> it finally loaded for her the second we got off the phone with  
> you.... but it gave some WEIRD info... she is looking for the number  
> of females who had a "D" code for their cycle in every month for  
> like.... forever. Ambitious  I know.  Thanks so much for looking at  
> this, we both really appreciate it.

She probably wants:

select count(cycpoints.cid), members.grp,
date_part('month', members.date) as month,
date_part('year', members.date) as year from
cycpoints, cycles, members where members.sname =
cycles.sname and cycles.cid = cycpoints.cid and
cycpoints.code='D' group by members.grp, month, year;

Which is the same as:

select count(cycpoints.cid), members.grp,
date_part('month', members.date) as month,
date_part('year', members.date) as year from
cycpoints, cycles, members where members.sname =
cycles.sname and cycles.cid = cycpoints.cid and
cycpoints.code='D' group by members.grp,
date_part('month', members.date),
date_part('year', members.date);

(I don't know just when you can use "foo"
when you rename columns with "AS foo", but
it seems to always work with the GROUP BY.)

It's also the same as using count(*) to count rows:

select count(*), members.grp,
date_part('month', members.date) as month,
date_part('year', members.date) as year from
cycpoints, cycles, members where members.sname =
cycles.sname and cycles.cid = cycpoints.cid and
cycpoints.code='D' group by members.grp, month, year;

You could also use the view that already joins
cycpoints and cycles, but if you don't it
can be marginally faster under some conditions:

select count(cycpoints_cycles.cid)
      , members.grp
      , date_part('month', members.date) as month
      , date_part('year', members.date) as year
   from cycpoints_cycles, members
   where members.sname = cypoints_cycles.sname
         and cycpoints_cycles.code='D'
   group by members.grp, month, year;

(I find formatting the SQL makes it easier
to read.)

Karl <kop at meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein



More information about the Babase mailing list