From: Peter Brawley
Date: October 28 2006 1:22pm
Subject: Re: How to summarize weekly totals?
List-Archive: http://lists.mysql.com/mysql/202988
Message-Id: <45435979.6030400@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-4543597A6A75======="
--=======AVGMAIL-4543597A6A75=======
Content-Type: multipart/alternative; boundary=------------080905030508090707030309
--------------080905030508090707030309
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
/>Some days may be missing if it is a holiday, which means I can't
>guarantee each week will have a sales row for Monday or Saturday.
>Do I need to write a program to do this? Or can it be done with SQL? TIA
/
Group by WEEK(sale_date), or build a calendar table and join from it.
PB
mos wrote:
> I can't get my head around this, but I think I should be able to do it
> using just SQL without writing a program.
>
> I have a simple table that looks like:
>
> Rcd_Id: Integer;
> Sale_Date: Date;
> Sale_Amt: Double;
>
> Each date will have 1 row with the total sales for that day.
>
> and I want to sum the weekly totals so it looks like:
>
> WeekEnding TotalAmt
> 2006-10-14 3899.34
> 2006-10-21 222.12
> 2006-10-28 33122.12
>
> So for each week, it will sum the sales for Monday through Saturday
> and provides a total column. It will do this for all the rows in the
> table. If there was a years worth of data, there would be 52 weeks
> hence 52 rows of data would be produced.
>
> Some days may be missing if it is a holiday, which means I can't
> guarantee each week will have a sales row for Monday or Saturday.
>
> Do I need to write a program to do this? Or can it be done with SQL? TIA
>
> Mike
>
--------------080905030508090707030309
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
>Some days may be missing if it is a holiday, which means I can't
>guarantee each week will have a sales row for Monday or Saturday.
>Do I need to write a program to do this? Or can it be done with
SQL? TIA
Group by WEEK(sale_date), or build a calendar table and join from it.
PB
mos wrote:
I can't get my head around this, but I think I should be
able to do it using just SQL without writing a program.
I have a simple table that looks like:
Rcd_Id: Integer;
Sale_Date: Date;
Sale_Amt: Double;
Each date will have 1 row with the total sales for that day.
and I want to sum the weekly totals so it looks like:
WeekEnding TotalAmt
2006-10-14 3899.34
2006-10-21 222.12
2006-10-28 33122.12
So for each week, it will sum the sales for Monday through Saturday and
provides a total column. It will do this for all the rows in the table.
If there was a years worth of data, there would be 52 weeks hence 52
rows of data would be produced.
Some days may be missing if it is a holiday, which means I can't
guarantee each week will have a sales row for Monday or Saturday.
Do I need to write a program to do this? Or can it be done with SQL?
TIA
Mike
--------------080905030508090707030309--
--=======AVGMAIL-4543597A6A75=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.408 / Virus Database: 268.13.16/504 - Release Date: 10/27/2006
=
--=======AVGMAIL-4543597A6A75=======--