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

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

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" <paul.laut...@btinternet.com>
wrote:
> laredotorn...@zipmail.com wrote:
> > 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, - DaveWith 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

laredotornado@zipmail.com wrote:
> On Jan 29, 3:01 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>> laredotorn...@zipmail.com wrote:
>>> 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, - DaveWith 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
> 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
>
First a request. Please do not top post!

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

Paul Lautman wrote:
> laredotornado@zipmail.com wrote:
>> On Jan 29, 3:01 pm, "Paul Lautman" <paul.laut...@btinternet.com>
>> wrote:
>>> laredotorn...@zipmail.com wrote:
>>>> 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, - DaveWith 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
>> 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
>>
> First a request. Please do not top post!
>
> You're right, I missed that. My sample data was incomplete.
> Thinking...

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

On Jan 29, 4:53 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> Paul Lautman wrote:
> > laredotorn...@zipmail.com wrote:
> >> On Jan 29, 3:01 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> >> wrote:
> >>> laredotorn...@zipmail.com wrote:
> >>>> 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, - DaveWith 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
> >> 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
>
> > First a request. Please do not top post!
>
> > You're right, I missed that. My sample data was incomplete.
> > Thinking...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

That did it. Thanks so much! -

On 29 Jan, 23:32, "laredotorn...@zipmail.com"
<laredotorn...@zipmail.com> wrote:
> On Jan 29, 4:53 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>
>
>
>
>
> > Paul Lautman wrote:
> > > laredotorn...@zipmail.com wrote:
> > >> On Jan 29, 3:01 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> > >> wrote:
> > >>> laredotorn...@zipmail.com wrote:
> > >>>> 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, - DaveWith 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
> > >> 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
>
> > > First a request. Please do not top post!
>
> > > You're right, I missed that. My sample data was incomplete.
> > > Thinking...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.DThat did it. Thanks so much! -- Hide quoted text -- Show quoted text -

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

On 29 Jan, 23:32, "laredotorn...@zipmail.com"
<laredotorn...@zipmail.com> wrote:
> On Jan 29, 4:53 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>
>
>
>
>
> > Paul Lautman wrote:
> > > laredotorn...@zipmail.com wrote:
> > >> On Jan 29, 3:01 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> > >> wrote:
> > >>> laredotorn...@zipmail.com wrote:
> > >>>> 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, - DaveWith 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
> > >> 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
>
> > > First a request. Please do not top post!
>
> > > You're right, I missed that. My sample data was incomplete.
> > > Thinking...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.DThat did it. Thanks so much! -- Hide quoted text -- Show quoted text -

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