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?
...