[Babase] Re: query from Marie

Lacey Maryott lacey.maryott at duke.edu
Thu May 10 15:14:05 EDT 2007


Karl,
   Marie says thank you very much!  and so do I! :)

Lacey

Karl O. Pinc wrote:
> 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
>
> _______________________________________________
> Babase mailing list
> Babase at www.eco.princeton.edu
> http://www.eco.princeton.edu/mailman/listinfo/babase
>

-- 
Lacey Maryott
Alberts Lab
Department of Biology
Duke University
ph: 919-660-7306
fax: 919-660-7293
Lacey.Maryott at duke.edu 



More information about the Babase mailing list