Sagewire Logo

distinct in a group by Date

8 Message(s) by 4 Author(s) originally posted in mysql discussion


From: sparky Date:   Thursday, October 18, 2007
I have the following view, and'd 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 sum s 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 not quite figure
it out.


From: Paul Lautman Date:   Thursday, October 18, 2007
wrote in message:
I have the following view, and'd 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 not quite figure
it out.



Your description is not that clear. Can you give a sample of your required
output from the above data .


From: sparky Date:   Thursday, October 18, 2007
On Oct 18, 3:25 pm, "Paul Lautman" <paul.laut...@xxxxxxxxxxx>
wrote in message:
wrote in message:
> I have the following view, and'd 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 not quite figure
> it out.
Your description is not 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


From: Captain Paralytic Date:   Friday, October 19, 2007
wrote in message:
On Oct 18, 3:25 pm, "Paul Lautman" <paul.laut...@xxxxxxxxxxx>
wrote in message:
wrote in message:
> > I have the following view, and'd 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 not quite figure
> > it out.
> Your description is not 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 did not understand?


From: sparky Date:   Friday, October 19, 2007
wrote in message:
wrote in message:
> On Oct 18, 3:25 pm, "Paul Lautman" <paul.laut...@xxxxxxxxxxx>
wrote in message:
wrote in message:
> > > I have the following view, and'd 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 not quite figure
> > > it out.
> > Your description is not 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 did not understand?



Sounds like you probably didn't 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 - isn't shown above

grouped by month
15000 10/18/2007
15700 11/18/2007
16000 12/18/2007If I do just a group by from the data, the sum(size) will be way too
big (it'll be right per day, but not per week or month).


From: strawberry Date:   Saturday, October 20, 2007
Sounds like you probably didn't even ready my post.



No, it sounds like YOU did not read HIS!


From: Paul Lautman Date:   Saturday, October 20, 2007
wrote in message:
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!


From: sparky Date:   Saturday, October 20, 2007
On Oct 20, 8:31 am, "Paul Lautman" <paul.laut...@xxxxxxxxxxx>
wrote in message:
wrote in message:
> 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've posted more lines in the first post so you could
see better from what data I wanted to derive this result.



Next Message: Select Into Question for Stored Procedure


Blogs related to distinct in a group by Date

sql do loop
... date sql greater than equal to sql greater than less than sql greater than or equal sql greater than or equal to sql greater than syntax sql grinder sql group sql group by sql group by clause sql group by count sql group by date sql ...

sql between date
... update sql count sql count 1 sql count command sql count distinct sql count distinct access sql count distinct group by sql count distinct multiple columns sql count distinct records sql count distinct rows sql count distinct values ...

this is not a mysql specific question but more of a general ...
select item,max(created) group by item. right, of course. but apart from that, there was no way. I was referring to a recent issue of mine, where I had to group by date and month having only a full datetime field to group on ...

mysql socket file
mysql subtract date mysql subtract dates mysql subtract time mysql subtract two dates mysql subtracting dates mysql subtraction mysql sum mysql sum column mysql sum count mysql sum distinct mysql sum function mysql sum functions ...

mysql redhat 7.3
... select date format mysql select date now mysql select date range mysql select date_format mysql select datediff mysql select dates mysql select datetime mysql select day mysql select db mysql select distinct mysql select distinct ...

i have b2evolution a database design question - i want to store ...
so there’s no way to set a default value for the current date. evenstar, argh what is it with the coloring? How can I tell MySQL to randomly NOT include some rows it would normally gather in that query? its my script, sorry. distinct? ...


Programming | Sports | Autos

copyright 2006
Valid XHTML 1.0 Transitional