Professional Web Applications Themes

help required with join.. - MySQL

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

  1. #1

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

    JimmyHoffa Guest

  2. #2

    Default Re: help required with join..

    On Jun 11, 2:28 pm, JimmyHoffa <com> wrote: 

    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.

    strawberry Guest

  3. #3

    Default Re: help required with join..

    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 <com> wrote: 







    >
    > 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.[/ref]


    JimmyHoffa Guest

  4. #4

    Default Re: help required with join..

    On Jun 11, 4:02 pm, JimmyHoffa <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

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

    strawberry Guest

  5. #5

    Default Re: help required with join..

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

    JimmyHoffa Guest

  6. #6

    Default Re: help required with join..

    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 <com> wrote: 


    JimmyHoffa Guest

  7. #7

    Default Re: help required with join..

    On Jun 11, 4:36 pm, JimmyHoffa <com> wrote: 






    > [/ref]

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

    strawberry Guest

Similar Threads

  1. Replies: 2
    Last Post: September 18th, 09:59 PM
  2. outer join, cross join, union? DDL/sample data incl.
    By Kevin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 08:01 PM
  3. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM
  4. Select Left Join AND Right Join
    By David in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 15th, 03:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139