help required with join..

Hi All, hopeing someone can help with a query...

I have a query that returns a count of bugs reported from customers
and what day of the month they occured on. So I get a resultset such
as

Count | DayOfMonth
25 | 1
30 | 5
1 | 8

etc..

What I want to do is amend the query so it returns values for
"everyday" of the current month. E.g.
25 | 1
0 | 2
0 | 3
0 | 4
30 | 5 and so on...

My query as it stands now is

select count(id) as "cnt",day(timestamp) as "day" from
error_logs
where YEAR(timestamp) = YEAR(NOW()) and month(timestamp) =
month(now())
group by day(timestamp);

Can anyone help me solve this ? Thanks...

On Jun 11, 2:28 pm, JimmyHoffa <photos.d...@googlemail.com> wrote:
> Hi All, hopeing someone can help with a query...
>
> I have a query that returns a count of bugs reported from customers
> and what day of the month they occured on. So I get a resultset such
> as
>
> Count | DayOfMonth
> 25 | 1
> 30 | 5
> 1 | 8
>
> etc..
>
> What I want to do is amend the query so it returns values for
> "everyday" of the current month. E.g.
> 25 | 1
> 0 | 2
> 0 | 3
> 0 | 4
> 30 | 5 and so on...
>
> My query as it stands now is
>
> select count(id) as "cnt",day(timestamp) as "day" from
> error_logs
> where YEAR(timestamp) = YEAR(NOW()) and month(timestamp) =
> month(now())
> group by day(timestamp);
>
> Can anyone help me solve this ? Thanks...

Typically, for a pure sql solution, you would first need to construct
a calendar table with all the relevant dates fed in to it and then
LEFT JOIN your query to that table.

Hi, I've just been trying that approach actually, I created a 'pivot'
table. with 1 column called idx, and populated it with 31 rows where
the idx is 1 to 31 respectively..

However the join doesnt seem to have any effect.. This is my latest
query...

select p.idx as "dayidx",count(el.timestamp),day(el.timestamp) as
"theday"
from error_logs el
join pivot p on (p.idx = day(el.timestamp))
where YEAR(el.timestamp) = YEAR(NOW()) and month(el.timestamp) =
month(now())
group by p.idx;

thx

On Jun 11, 2:36 pm, strawberry <zac.ca...@gmail.com> wrote:
> On Jun 11, 2:28 pm, JimmyHoffa <photos.d...@googlemail.com> wrote:
>
>
>
> > Hi All, hopeing someone can help with a query...
>
> > I have a query that returns a count of bugs reported from customers
> > and what day of the month they occured on. So I get a resultset such
> > as
>
> > Count | DayOfMonth
> > 25 | 1
> > 30 | 5
> > 1 | 8
>
> > etc..
>
> > What I want to do is amend the query so it returns values for
> > "everyday" of the current month. E.g.
> > 25 | 1
> > 0 | 2
> > 0 | 3
> > 0 | 4
> > 30 | 5 and so on...
>
> > My query as it stands now is
>
> > select count(id) as "cnt",day(timestamp) as "day" from
> > error_logs
> > where YEAR(timestamp) = YEAR(NOW()) and month(timestamp) =
> > month(now())
> > group by day(timestamp);
>
> > Can anyone help me solve this ? Thanks...
>
> Typically, for a pure sql solution, you would first need to construct
> a calendar table with all the relevant dates fed in to it and then
> LEFT JOIN your query to that table.

On Jun 11, 4:02 pm, JimmyHoffa <photos.d...@googlemail.com> wrote:
> Hi, I've just been trying that approach actually, I created a 'pivot'
> table. with 1 column called idx, and populated it with 31 rows where
> the idx is 1 to 31 respectively..
>
> However the join doesnt seem to have any effect.. This is my latest
> query...
>
> select p.idx as "dayidx",count(el.timestamp),day(el.timestamp) as
> "theday"
> from error_logs el
> join pivot p on (p.idx = day(el.timestamp))
> where YEAR(el.timestamp) = YEAR(NOW()) and month(el.timestamp) =
> month(now())
> group by p.idx;
>
> thx
>
> On Jun 11, 2:36 pm, strawberry <zac.ca...@gmail.com> wrote:
>
> > On Jun 11, 2:28 pm, JimmyHoffa <photos.d...@googlemail.com> wrote:
>
> > > Hi All, hopeing someone can help with a query...
>
> > > I have a query that returns a count of bugs reported from customers
> > > and what day of the month they occured on. So I get a resultset such
> > > as
>
> > > Count | DayOfMonth
> > > 25 | 1
> > > 30 | 5
> > > 1 | 8
>
> > > etc..
>
> > > What I want to do is amend the query so it returns values for
> > > "everyday" of the current month. E.g.
> > > 25 | 1
> > > 0 | 2
> > > 0 | 3
> > > 0 | 4
> > > 30 | 5 and so on...
>
> > > My query as it stands now is
>
> > > select count(id) as "cnt",day(timestamp) as "day" from
> > > error_logs
> > > where YEAR(timestamp) = YEAR(NOW()) and month(timestamp) =
> > > month(now())
> > > group by day(timestamp);
>
> > > Can anyone help me solve this ? Thanks...
>
> > Typically, for a pure sql solution, you would first need to construct
> > a calendar table with all the relevant dates fed in to it and then
> > LEFT JOIN your query to that table.

Well, I didn't say "JOIN" did I? ;-)

I've stripped the current month filter from the query so this should
in theory work ;

SELECT p.idx AS "dayidx",COUNT(el.timestamp)
FROM error_logs el
LEFT JOIN pivot p ON (p.idx = DAY(el.timestamp))
GROUP BY DAY(el.timestamp);

but it still only rows when there is a matching (day) in the
error_logs file.. I'm wondering if its some mysql groupby & join
issue..

Got there eventually, this works...

select p.idx as "dayidx",count(day(el.timestamp))
from error_logs el
right join pivot p on day(el.timestamp) = p.idx
group by p.idx
order by p.idx;



On Jun 11, 2:28 pm, JimmyHoffa <photos.d...@googlemail.com> wrote:
> Hi All, hopeing someone can help with a query...
>
> I have a query that returns a count of bugs reported from customers
> and what day of the month they occured on. So I get a resultset such
> as
>
> Count | DayOfMonth
> 25 | 1
> 30 | 5
> 1 | 8
>
> etc..
>
> What I want to do is amend the query so it returns values for
> "everyday" of the current month. E.g.
> 25 | 1
> 0 | 2
> 0 | 3
> 0 | 4
> 30 | 5 and so on...
>
> My query as it stands now is
>
> select count(id) as "cnt",day(timestamp) as "day" from
> error_logs
> where YEAR(timestamp) = YEAR(NOW()) and month(timestamp) =
> month(now())
> group by day(timestamp);
>
> Can anyone help me solve this ? Thanks...

On Jun 11, 4:36 pm, JimmyHoffa <photos.d...@googlemail.com> wrote:
> Got there eventually, this works...
>
> select p.idx as "dayidx",count(day(el.timestamp))
> from error_logs el
> right join pivot p on day(el.timestamp) = p.idx
> group by p.idx
> order by p.idx;
>
> On Jun 11, 2:28 pm, JimmyHoffa <photos.d...@googlemail.com> wrote:
>
> > Hi All, hopeing someone can help with a query...
>
> > I have a query that returns a count of bugs reported from customers
> > and what day of the month they occured on. So I get a resultset such
> > as
>
> > Count | DayOfMonth
> > 25 | 1
> > 30 | 5
> > 1 | 8
>
> > etc..
>
> > What I want to do is amend the query so it returns values for
> > "everyday" of the current month. E.g.
> > 25 | 1
> > 0 | 2
> > 0 | 3
> > 0 | 4
> > 30 | 5 and so on...
>
> > My query as it stands now is
>
> > select count(id) as "cnt",day(timestamp) as "day" from
> > error_logs
> > where YEAR(timestamp) = YEAR(NOW()) and month(timestamp) =
> > month(now())
> > group by day(timestamp);
>
> > Can anyone help me solve this ? Thanks...

....which is syntactically identical to:

select p.idx as "dayidx",count(day(el.timestamp))
from pivot p left join error_logs el
on day(el.timestamp) = p.idx
group by p.idx
order by p.idx;