I have the following view, and would like to do a group by date to
find out what is the current size of production. I can do the group
by week (dt), but that returns 1 barn 7 times per week, so it sums it
up 7 times. How can I do this group by and use the distinct to have
only one barn per grouping?

ID Barn_ID Size StartDate Received StartDate_p_Life dt
1 1 5000 08/02/2007 1 22/10/2007 10/18/2007
5 2 4000 17/10/2007 1 12/08/2008 10/18/2007
6 3 6000 15/05/2007 1 11/11/2007 10/18/2007
1 1 5000 08/02/2007 1 22/10/2007 10/19/2007
5 2 4000 17/10/2007 1 12/08/2008 10/19/2007
6 3 6000 15/05/2007 1 11/11/2007 10/19/2007
1 1 5000 08/02/2007 1 22/10/2007 10/20/2007
5 2 4000 17/10/2007 1 12/08/2008 10/20/2007
6 3 6000 15/05/2007 1 11/11/2007 10/20/2007

maybe it's not even possible to use the distint - I can't quite figure
it out.

sparky wrote:
> I have the following view, and would like to do a group by date to
> find out what is the current size of production. I can do the group
> by week (dt), but that returns 1 barn 7 times per week, so it sums it
> up 7 times. How can I do this group by and use the distinct to have
> only one barn per grouping?
>
> ID Barn_ID Size StartDate Received StartDate_p_Life dt
> 1 1 5000 08/02/2007 1 22/10/2007 10/18/2007
> 5 2 4000 17/10/2007 1 12/08/2008 10/18/2007
> 6 3 6000 15/05/2007 1 11/11/2007 10/18/2007
> 1 1 5000 08/02/2007 1 22/10/2007 10/19/2007
> 5 2 4000 17/10/2007 1 12/08/2008 10/19/2007
> 6 3 6000 15/05/2007 1 11/11/2007 10/19/2007
> 1 1 5000 08/02/2007 1 22/10/2007 10/20/2007
> 5 2 4000 17/10/2007 1 12/08/2008 10/20/2007
> 6 3 6000 15/05/2007 1 11/11/2007 10/20/2007
>
> maybe it's not even possible to use the distint - I can't quite figure
> it out.

Your description isn't that clear. Can you give a sample of your required
output from the above data.

On Oct 18, 3:25 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> sparky wrote:
> > I have the following view, and would like to do a group by date to
> > find out what is the current size of production. I can do the group
> > by week (dt), but that returns 1 barn 7 times per week, so it sums it
> > up 7 times. How can I do this group by and use the distinct to have
> > only one barn per grouping?
>
> > ID Barn_ID Size StartDate Received StartDate_p_Life dt
> > 1 1 5000 08/02/2007 1 22/10/2007 10/18/2007
> > 5 2 4000 17/10/2007 1 12/08/2008 10/18/2007
> > 6 3 6000 15/05/2007 1 11/11/2007 10/18/2007
> > 1 1 5000 08/02/2007 1 22/10/2007 10/19/2007
> > 5 2 4000 17/10/2007 1 12/08/2008 10/19/2007
> > 6 3 6000 15/05/2007 1 11/11/2007 10/19/2007
> > 1 1 5000 08/02/2007 1 22/10/2007 10/20/2007
> > 5 2 4000 17/10/2007 1 12/08/2008 10/20/2007
> > 6 3 6000 15/05/2007 1 11/11/2007 10/20/2007
>
> > maybe it's not even possible to use the distint - I can't quite figure
> > it out.
>
> Your description isn't that clear. Can you give a sample of your required
> output from the above data.

What I want to get is a sum of hens that for each day if I group by
day, which in this case is no problem. But when I group by week, I
have barn #1 listed each day, so if you just do a simple group by per
week, it adds up barn #1 seven times, but I only want it once. The
same goes for month. I guess I could do a where clause and set it to
retrieve only the first day of the week, and then the grouping should
work, right? How can I only the first day of the week?
Thanks

On 19 Oct, 01:14, sparky <markdueck...@gmail.com> wrote:
> On Oct 18, 3:25 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>
>
>
>
>
> > sparky wrote:
> > > I have the following view, and would like to do a group by date to
> > > find out what is the current size of production. I can do the group
> > > by week (dt), but that returns 1 barn 7 times per week, so it sums it
> > > up 7 times. How can I do this group by and use the distinct to have
> > > only one barn per grouping?
>
> > > ID Barn_ID Size StartDate Received StartDate_p_Life dt
> > > 1 1 5000 08/02/2007 1 22/10/2007 10/18/2007
> > > 5 2 4000 17/10/2007 1 12/08/2008 10/18/2007
> > > 6 3 6000 15/05/2007 1 11/11/2007 10/18/2007
> > > 1 1 5000 08/02/2007 1 22/10/2007 10/19/2007
> > > 5 2 4000 17/10/2007 1 12/08/2008 10/19/2007
> > > 6 3 6000 15/05/2007 1 11/11/2007 10/19/2007
> > > 1 1 5000 08/02/2007 1 22/10/2007 10/20/2007
> > > 5 2 4000 17/10/2007 1 12/08/2008 10/20/2007
> > > 6 3 6000 15/05/2007 1 11/11/2007 10/20/2007
>
> > > maybe it's not even possible to use the distint - I can't quite figure
> > > it out.
>
> > Your description isn't that clear. Can you give a sample of your required
> > output from the above data.
>
> What I want to get is a sum of hens that for each day if I group by
> day, which in this case is no problem. But when I group by week, I
> have barn #1 listed each day, so if you just do a simple group by per
> week, it adds up barn #1 seven times, but I only want it once. The
> same goes for month. I guess I could do a where clause and set it to
> retrieve only the first day of the week, and then the grouping should
> work, right? How can I only the first day of the week?
> Thanks- Hide quoted text -
>
> - Show quoted text -

Which part of "Can you give a sample of your required output from the
above data." was it that you didn't understand?

On Oct 19, 2:25 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 19 Oct, 01:14, sparky <markdueck...@gmail.com> wrote:
>
>
>
> > On Oct 18, 3:25 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> > wrote:
>
> > > sparky wrote:
> > > > I have the following view, and would like to do a group by date to
> > > > find out what is the current size of production. I can do the group
> > > > by week (dt), but that returns 1 barn 7 times per week, so it sums it
> > > > up 7 times. How can I do this group by and use the distinct to have
> > > > only one barn per grouping?
>
> > > > ID Barn_ID Size StartDate Received StartDate_p_Life dt
> > > > 1 1 5000 08/02/2007 1 22/10/2007 10/18/2007
> > > > 5 2 4000 17/10/2007 1 12/08/2008 10/18/2007
> > > > 6 3 6000 15/05/2007 1 11/11/2007 10/18/2007
> > > > 1 1 5000 08/02/2007 1 22/10/2007 10/19/2007
> > > > 5 2 4000 17/10/2007 1 12/08/2008 10/19/2007
> > > > 6 3 6000 15/05/2007 1 11/11/2007 10/19/2007
> > > > 1 1 5000 08/02/2007 1 22/10/2007 10/20/2007
> > > > 5 2 4000 17/10/2007 1 12/08/2008 10/20/2007
> > > > 6 3 6000 15/05/2007 1 11/11/2007 10/20/2007
>
> > > > maybe it's not even possible to use the distint - I can't quite figure
> > > > it out.
>
> > > Your description isn't that clear. Can you give a sample of your required
> > > output from the above data.
>
> > What I want to get is a sum of hens that for each day if I group by
> > day, which in this case is no problem. But when I group by week, I
> > have barn #1 listed each day, so if you just do a simple group by per
> > week, it adds up barn #1 seven times, but I only want it once. The
> > same goes for month. I guess I could do a where clause and set it to
> > retrieve only the first day of the week, and then the grouping should
> > work, right? How can I only the first day of the week?
> > Thanks- Hide quoted text -
>
> > - Show quoted text -
>
> Which part of "Can you give a sample of your required output from the
> above data." was it that you didn't understand?

Sounds like you probably did not even ready my post.

What I want from the data above is this:
grouped by day:
sum(size) dt
15000 10/18/2007
15000 10/19/2007
15000 10/20/2007

grouped by week
15000 10/18/2007
15000 10/25/2007 - is not shown above

grouped by month
15000 10/18/2007
15700 11/18/2007
16000 12/18/2007


If I do just a group by from the data, the sum(size) will be way too
big (it will be right per day, but not per week or month).

>
> Sounds like you probably did not even ready my post.
>

No, it sounds like YOU didn't read HIS!

sparky wrote:
> lots of crap

Your ability to describe what you want in prose is sadly lacking.

I asked you to post a sample of the required output, instead of which you
posted more unintelligble crap. When I pointed out that you had singularly
faild to post what I asked for, you accused me of not having read your post.
So as far as I am concerned, you can p*ss *ff and solve your own problem!

On Oct 20, 8:31 am, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> sparky wrote:
> > lots of crap
>
> Your ability to describe what you want in prose is sadly lacking.
>
> I asked you to post a sample of the required output, instead of which you
> posted more unintelligble crap. When I pointed out that you had singularly
> faild to post what I asked for, you accused me of not having read your post.
> So as far as I am concerned, you can p*ss *ff and solve your own problem!

I already solved it now.. - The 'unintelligble crap' is EXACTLY
the result I wanted, and I got it by doing a group by dt and a "where
clause" of 'where dayofmonth(dt) = 15' or 'where dayofweek(dt) = 3'
for middle of month and middle of week respectively.

Maybe I should have posted more lines in the first post so you could
see better from what data I wanted to derive this result.