Professional Web Applications Themes

How to write a query for each days sales, even if no sales on that day? - MySQL

Hi, I'm using MySQL 5.0. I have three tables -- SALES_PER_DAY, CALENDAR_DAYS, and USERS, which contain SALES_PER_DAY --------------------------- D DATE, USER_ID INTEGER, SALES FLOAT UNSIGNED PRIMARY KEY (D, USER_ID) CALENDAR_DAYS ---------------------------- D DATE USERS ----------- USER_ID INTEGER GROUP_ID INTEGER There is not necessarily sales for each day. The "CALENDAR_DAYS" table contains one row for each day of the year (There are 365 rows that contain the year "2005"). However, I want to write a query that, given a date range and a user group ID, produces a row of data (with day and total sales) for each day in the ...

  1. #1

    Default How to write a query for each days sales, even if no sales on that day?

    Hi,

    I'm using MySQL 5.0. I have three tables -- SALES_PER_DAY,
    CALENDAR_DAYS, and USERS, which contain

    SALES_PER_DAY
    ---------------------------
    D DATE,
    USER_ID INTEGER,
    SALES FLOAT UNSIGNED
    PRIMARY KEY (D, USER_ID)

    CALENDAR_DAYS
    ----------------------------
    D DATE

    USERS
    -----------
    USER_ID INTEGER
    GROUP_ID INTEGER

    There is not necessarily sales for each day. The "CALENDAR_DAYS"
    table contains one row for each day of the year (There are 365 rows
    that contain the year "2005"). However, I want to write a query that,
    given a date range and a user group ID, produces a row of data (with
    day and total sales) for each day in the date range with a zero, if no
    sales were made on that day.

    How can I do this?

    Thanks, - Dave

    laredotornado@zipmail.com Guest

  2. #2

    Default Re: How to write a query for each days sales, even if no sales on that day?

    com wrote: 

    With something like this:

    SELECT c.D, IFNULL( SUM( s.SALES) , 0)
    FROM CALENDAR_DAYS c
    LEFT JOIN SALES_PER_DAY s USING(D)
    LEFT JOIN USERS u ON s.USER_ID = u.USER_ID AND u.GROUP_ID =1
    WHERE c.D BETWEEN '2005-01-01' AND '2005-01-10'
    GROUP BY c.D


    Paul Guest

  3. #3

    Default Re: How to write a query for each days sales, even if no sales on that day?

    Thanks but sadly this query does not work. Although it does return
    the correct number of rows, it returns sales from users other than the
    one specified.

    Any other suggestions are greatly appreciated - Dave

    On Jan 29, 3:01 pm, "Paul Lautman" <com>
    wrote: 







    >
    > SELECT c.D, IFNULL( SUM( s.SALES) , 0)
    > FROM CALENDAR_DAYS c
    > LEFT JOIN SALES_PER_DAY s USING(D)
    > LEFT JOIN USERS u ON s.USER_ID = u.USER_ID AND u.GROUP_ID =1
    > WHERE c.D BETWEEN '2005-01-01' AND '2005-01-10'
    > GROUP BY c.D[/ref]

    laredotornado@zipmail.com Guest

  4. #4

    Default Re: How to write a query for each days sales, even if no sales on that day?

    com wrote: 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >>
    >> SELECT c.D, IFNULL( SUM( s.SALES) , 0)
    >> FROM CALENDAR_DAYS c
    >> LEFT JOIN SALES_PER_DAY s USING(D)
    >> LEFT JOIN USERS u ON s.USER_ID = u.USER_ID AND u.GROUP_ID =1
    >> WHERE c.D BETWEEN '2005-01-01' AND '2005-01-10'
    >> GROUP BY c.D[/ref]
    > Thanks but sadly this query does not work. Although it does return
    > the correct number of rows, it returns sales from users other than the
    > one specified.
    >
    > Any other suggestions are greatly appreciated - Dave
    >[/ref]
    First a request. Please do not top post!

    You're right, I missed that. My sample data was incomplete. Thinking...


    Paul Guest

  5. #5

    Default Re: How to write a query for each days sales, even if no sales on that day?

    Paul Lautman wrote: 
    >> Thanks but sadly this query does not work. Although it does return
    >> the correct number of rows, it returns sales from users other than
    >> the one specified.
    >>
    >> Any other suggestions are greatly appreciated - Dave
    >>[/ref]
    > First a request. Please do not top post!
    >
    > You're right, I missed that. My sample data was incomplete.
    > Thinking...[/ref]

    OK try this:

    SELECT c.D, SUM( IF( u.GROUP_ID =1, s.SALES, 0 ) )
    FROM CALENDAR_DAYS c
    LEFT JOIN SALES_PER_DAY s USING ( D )
    LEFT JOIN USERS u ON s.USER_ID = u.USER_ID
    WHERE c.D BETWEEN '2005-01-01' AND '2005-01-10'
    GROUP BY c.D


    Paul Guest

  6. #6

    Default Re: How to write a query for each days sales, even if no sales on that day?



    On Jan 29, 4:53 pm, "Paul Lautman" <com>
    wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > SELECT c.D, SUM( IF( u.GROUP_ID =1, s.SALES, 0 ) )
    > FROM CALENDAR_DAYS c
    > LEFT JOIN SALES_PER_DAY s USING ( D )
    > LEFT JOIN USERS u ON s.USER_ID = u.USER_ID
    > WHERE c.D BETWEEN '2005-01-01' AND '2005-01-10'
    > GROUP BY c.D[/ref]

    That did it. Thanks so much! -

    laredotornado@zipmail.com Guest

  7. #7

    Default Re: How to write a query for each days sales, even if no sales on that day?



    On 29 Jan, 23:32, "com"
    <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    Could of course shrink that slightly to:

    SELECT c.D, SUM( IF( u.GROUP_ID =1, s.SALES, 0 ) )
    FROM CALENDAR_DAYS c
    LEFT JOIN SALES_PER_DAY s USING ( D )
    LEFT JOIN USERS u USING(USER_ID)
    WHERE c.D BETWEEN '2005-01-01' AND '2005-01-10'
    GROUP BY c.D

    Captain Guest

  8. #8

    Default Re: How to write a query for each days sales, even if no sales on that day?



    On 29 Jan, 23:32, "com"
    <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    Could of course shrink that slightly to:

    SELECT c.D, SUM( IF( u.GROUP_ID =1, s.SALES, 0 ) )
    FROM CALENDAR_DAYS c
    LEFT JOIN SALES_PER_DAY s USING ( D )
    LEFT JOIN USERS u USING(USER_ID)
    WHERE c.D BETWEEN '2005-01-01' AND '2005-01-10'
    GROUP BY c.D

    Captain Guest

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