Hi,

I'm trying to do a very convoluted query. I want to add subtract
values in pairs while selecting records and display the results in the
query like this:

SELECT customer_count, DATE_FORMAT(customer_date,'%a') dow,
DATE_FORMAT(customer_date,'%c/%e') fmt_date FROM cust_stats WHERE
DATE_FORMAT(customer_date,'%a') = 'Fri' ORDER BY customer_date ASC;

What I want to do, is for customer_count, I want to subtract the value
from the previous record value and get the result in the query.

So, say I have these records (for name sake)

John Doe | 14 | 2008-03-14
Jane Doe | 8 | 2008-04-01
Jack Doe | 12 | 2008-05-19
Jim Doe | 11 | 2008-06-10

I want to subtract Jane Doe from John Doe and get the result in the
query result. (-6)
I want to subtract Jack Doe from Jane Doe and get the result in the
query result. (4)
I want to subtract Jim Doe from Jack Doe and get the result in the
query result. (-1)

Can this be done?

Thanks you,

John

On 10 Jun, 13:30, Mtek <m...@mtekusa.com> wrote:
> Hi,
>
> I'm trying to do a very convoluted query. *I want to add subtract
> values in pairs while selecting records and display the results in the
> query like this:
>
> SELECT customer_count, DATE_FORMAT(customer_date,'%a') dow,
> DATE_FORMAT(customer_date,'%c/%e') fmt_date FROM cust_stats WHERE
> DATE_FORMAT(customer_date,'%a') = 'Fri' ORDER BY customer_date ASC;
>
> What I want to do, is for customer_count, I want to subtract the value
> from the previous record value and get the result in the query.
>
> So, say I have these records (for name sake)
>
> John Doe | 14 | 2008-03-14
> Jane Doe | 8 | 2008-04-01
> Jack Doe | 12 | 2008-05-19
> Jim *Doe | 11 | 2008-06-10
>
> I want to subtract Jane Doe from John Doe and get the result in the
> query result. *(-6)
> I want to subtract Jack Doe from Jane Doe and get the result in the
> query result. *(4)
> I want to subtract Jim Doe from Jack Doe and get the result in the
> query result. * *(-1)
>
> Can this be done?
>
> Thanks you,
>
> John

So, you decided not to persue the window cleaning then!

How are you defining a "previous record" and what relevance does
Friday have in this (since only one of the above dates are a Friday?

On Jun 10, 7:47 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 10 Jun, 13:30, Mtek <m...@mtekusa.com> wrote:
>
>
>
> > Hi,
>
> > I'm trying to do a very convoluted query. I want to add subtract
> > values in pairs while selecting records and display the results in the
> > query like this:
>
> > SELECT customer_count, DATE_FORMAT(customer_date,'%a') dow,
> > DATE_FORMAT(customer_date,'%c/%e') fmt_date FROM cust_stats WHERE
> > DATE_FORMAT(customer_date,'%a') = 'Fri' ORDER BY customer_date ASC;
>
> > What I want to do, is for customer_count, I want to subtract the value
> > from the previous record value and get the result in the query.
>
> > So, say I have these records (for name sake)
>
> > John Doe | 14 | 2008-03-14
> > Jane Doe | 8 | 2008-04-01
> > Jack Doe | 12 | 2008-05-19
> > Jim Doe | 11 | 2008-06-10
>
> > I want to subtract Jane Doe from John Doe and get the result in the
> > query result. (-6)
> > I want to subtract Jack Doe from Jane Doe and get the result in the
> > query result. (4)
> > I want to subtract Jim Doe from Jack Doe and get the result in the
> > query result. (-1)
>
> > Can this be done?
>
> > Thanks you,
>
> > John
>
> So, you decided not to persue the window cleaning then!
>
> How are you defining a "previous record" and what relevance does
> Friday have in this (since only one of the above dates are a Friday?


I'm just using dates in general. The actual table has about 500
records in it.

I order it by date, so I want to subtract the 'previous' record in the
returned record set. If it cannot be done, I can do it at the PHP
level, but I was just curious......

On 10 Jun, 14:19, Mtek <m...@mtekusa.com> wrote:
> On Jun 10, 7:47 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 10 Jun, 13:30, Mtek <m...@mtekusa.com> wrote:
>
> > > Hi,
>
> > > I'm trying to do a very convoluted query. *I want to add subtract
> > > values in pairs while selecting records and display the results in the
> > > query like this:
>
> > > SELECT customer_count, DATE_FORMAT(customer_date,'%a') dow,
> > > DATE_FORMAT(customer_date,'%c/%e') fmt_date FROM cust_stats WHERE
> > > DATE_FORMAT(customer_date,'%a') = 'Fri' ORDER BY customer_date ASC;
>
> > > What I want to do, is for customer_count, I want to subtract the value
> > > from the previous record value and get the result in the query.
>
> > > So, say I have these records (for name sake)
>
> > > John Doe | 14 | 2008-03-14
> > > Jane Doe | 8 | 2008-04-01
> > > Jack Doe | 12 | 2008-05-19
> > > Jim *Doe | 11 | 2008-06-10
>
> > > I want to subtract Jane Doe from John Doe and get the result in the
> > > query result. *(-6)
> > > I want to subtract Jack Doe from Jane Doe and get the result in the
> > > query result. *(4)
> > > I want to subtract Jim Doe from Jack Doe and get the result in the
> > > query result. * *(-1)
>
> > > Can this be done?
>
> > > Thanks you,
>
> > > John
>
> > So, you decided not to persue the window cleaning then!
>
> > How are you defining a "previous record" and what relevance does
> > Friday have in this (since only one of the above dates are a Friday?
>
> I'm just using dates in general. *The actual table has about 500
> records in it.
>
> I order it by date, so I want to subtract the 'previous' record in the
> returned record set. *If it cannot be done, I can do it at the PHP
> level, but I was just curious......- Hide quoted text -
>
> - Show quoted text -

I'm sure it can be done, but you are not describing what you want to
do very well and you did not answer my question about Fridays!

Also, on comp.lang.php you claimed that a search on Google Groups did
not find this group, yet you posted a question and found that you
first posted there on 26th March. So how can you claim that you didn't
know that there WAS such a group???

Getting back to your question. I need to ask you some more about the
table. Please have the curtesy to answer!

Please tell me, is it possible that 2 records can have the same date
or is it the case that the date field is a UNIQUE INDEX?
And please answer my question about Friday.

On Jun 10, 9:11 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 10 Jun, 14:19, Mtek <m...@mtekusa.com> wrote:
>
>
>
> > On Jun 10, 7:47 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
> > > On 10 Jun, 13:30, Mtek <m...@mtekusa.com> wrote:
>
> > > > Hi,
>
> > > > I'm trying to do a very convoluted query. I want to add subtract
> > > > values in pairs while selecting records and display the results in the
> > > > query like this:
>
> > > > SELECT customer_count, DATE_FORMAT(customer_date,'%a') dow,
> > > > DATE_FORMAT(customer_date,'%c/%e') fmt_date FROM cust_stats WHERE
> > > > DATE_FORMAT(customer_date,'%a') = 'Fri' ORDER BY customer_date ASC;
>
> > > > What I want to do, is for customer_count, I want to subtract the value
> > > > from the previous record value and get the result in the query.
>
> > > > So, say I have these records (for name sake)
>
> > > > John Doe | 14 | 2008-03-14
> > > > Jane Doe | 8 | 2008-04-01
> > > > Jack Doe | 12 | 2008-05-19
> > > > Jim Doe | 11 | 2008-06-10
>
> > > > I want to subtract Jane Doe from John Doe and get the result in the
> > > > query result. (-6)
> > > > I want to subtract Jack Doe from Jane Doe and get the result in the
> > > > query result. (4)
> > > > I want to subtract Jim Doe from Jack Doe and get the result in the
> > > > query result. (-1)
>
> > > > Can this be done?
>
> > > > Thanks you,
>
> > > > John
>
> > > So, you decided not to persue the window cleaning then!
>
> > > How are you defining a "previous record" and what relevance does
> > > Friday have in this (since only one of the above dates are a Friday?
>
> > I'm just using dates in general. The actual table has about 500
> > records in it.
>
> > I order it by date, so I want to subtract the 'previous' record in the
> > returned record set. If it cannot be done, I can do it at the PHP
> > level, but I was just curious......- Hide quoted text -
>
> > - Show quoted text -
>
> I'm sure it can be done, but you are not describing what you want to
> do very well and you did not answer my question about Fridays!
>
> Also, on comp.lang.php you claimed that a search on Google Groups did
> not find this group, yet you posted a question and found that you
> first posted there on 26th March. So how can you claim that you didn't
> know that there WAS such a group???
>
> Getting back to your question. I need to ask you some more about the
> table. Please have the curtesy to answer!
>
> Please tell me, is it possible that 2 records can have the same date
> or is it the case that the date field is a UNIQUE INDEX?
> And please answer my question about Friday.

Ok, all the records for a given date will be different. There will be
several in the database for Friday, Saturday, Monday, etc. I'll get
all for a Friday, or all for a Saturday and order them by date:

Fridays: 6/6, 6/13, 6/20, 6/27......

Then I want to compute the difference between a given record and it's
previous record based on the ordering......

If this cannot be done, I'll just do it at the PHP level, it is easy
there. But I was wondering if it can be done at the sql level.....

Mtek wrote:
> Hi,
>
> I'm trying to do a very convoluted query. I want to add subtract
> values in pairs while selecting records and display the results in the
> query like this:
>
> SELECT customer_count, DATE_FORMAT(customer_date,'%a') dow,
> DATE_FORMAT(customer_date,'%c/%e') fmt_date FROM cust_stats WHERE
> DATE_FORMAT(customer_date,'%a') = 'Fri' ORDER BY customer_date ASC;
>
> What I want to do, is for customer_count, I want to subtract the value
> from the previous record value and get the result in the query.
>
> So, say I have these records (for name sake)
>
> John Doe | 14 | 2008-03-14
> Jane Doe | 8 | 2008-04-01
> Jack Doe | 12 | 2008-05-19
> Jim Doe | 11 | 2008-06-10
>
> I want to subtract Jane Doe from John Doe and get the result in the
> query result. (-6)
> I want to subtract Jack Doe from Jane Doe and get the result in the
> query result. (4)
> I want to subtract Jim Doe from Jack Doe and get the result in the
> query result. (-1)

SET @previous = 0;
SELECT
customer_count - @previous as 'difference',
@previous := customer_count as 'customer_count',
DATE_FORMAT(customer_date,'%a') dow,
DATE_FORMAT(customer_date,'%c/%e') fmt_date
FROM cust_stats
WHERE DATE_FORMAT(customer_date,'%a') = 'Fri'
ORDER BY customer_date ASC;
--
Rik Wasmus
....spamrun finished

On 10 Jun, 15:31, Mtek <m...@mtekusa.com> wrote:
> Ok, all the records for a given date will be different.
Sheesh you are hard work.

I asked:
"Please tell me, is it possible that 2 records can have the same
date?"
You say "all the records for a given date". That suggests that there
can be more than one record with the same date.
So, if there are 3 records all with the date of 2008-03-14, then
please tell me how you define which of them is a "previous" one?

On Jun 10, 9:56 am, Rik Wasmus <luiheidsgoe...@hotmail.com> wrote:
> Mtek wrote:
> > Hi,
>
> > I'm trying to do a very convoluted query. I want to add subtract
> > values in pairs while selecting records and display the results in the
> > query like this:
>
> > SELECT customer_count, DATE_FORMAT(customer_date,'%a') dow,
> > DATE_FORMAT(customer_date,'%c/%e') fmt_date FROM cust_stats WHERE
> > DATE_FORMAT(customer_date,'%a') = 'Fri' ORDER BY customer_date ASC;
>
> > What I want to do, is for customer_count, I want to subtract the value
> > from the previous record value and get the result in the query.
>
> > So, say I have these records (for name sake)
>
> > John Doe | 14 | 2008-03-14
> > Jane Doe | 8 | 2008-04-01
> > Jack Doe | 12 | 2008-05-19
> > Jim Doe | 11 | 2008-06-10
>
> > I want to subtract Jane Doe from John Doe and get the result in the
> > query result. (-6)
> > I want to subtract Jack Doe from Jane Doe and get the result in the
> > query result. (4)
> > I want to subtract Jim Doe from Jack Doe and get the result in the
> > query result. (-1)
>
> SET @previous = 0;
> SELECT
> customer_count - @previous as 'difference',
> @previous := customer_count as 'customer_count',
> DATE_FORMAT(customer_date,'%a') dow,
> DATE_FORMAT(customer_date,'%c/%e') fmt_date
> FROM cust_stats
> WHERE DATE_FORMAT(customer_date,'%a') = 'Fri'
> ORDER BY customer_date ASC;
> --
> Rik Wasmus
> ...spamrun finished


Very slick Rik. I've never seen some of those things, like
@previous. I'm guessing some array?

Anyhow, thanks a bunch!!

John

On Jun 10, 9:56 am, Rik Wasmus <luiheidsgoe...@hotmail.com> wrote:
> Mtek wrote:
> > Hi,
>
> > I'm trying to do a very convoluted query. I want to add subtract
> > values in pairs while selecting records and display the results in the
> > query like this:
>
> > SELECT customer_count, DATE_FORMAT(customer_date,'%a') dow,
> > DATE_FORMAT(customer_date,'%c/%e') fmt_date FROM cust_stats WHERE
> > DATE_FORMAT(customer_date,'%a') = 'Fri' ORDER BY customer_date ASC;
>
> > What I want to do, is for customer_count, I want to subtract the value
> > from the previous record value and get the result in the query.
>
> > So, say I have these records (for name sake)
>
> > John Doe | 14 | 2008-03-14
> > Jane Doe | 8 | 2008-04-01
> > Jack Doe | 12 | 2008-05-19
> > Jim Doe | 11 | 2008-06-10
>
> > I want to subtract Jane Doe from John Doe and get the result in the
> > query result. (-6)
> > I want to subtract Jack Doe from Jane Doe and get the result in the
> > query result. (4)
> > I want to subtract Jim Doe from Jack Doe and get the result in the
> > query result. (-1)
>
> SET @previous = 0;
> SELECT
> customer_count - @previous as 'difference',
> @previous := customer_count as 'customer_count',
> DATE_FORMAT(customer_date,'%a') dow,
> DATE_FORMAT(customer_date,'%c/%e') fmt_date
> FROM cust_stats
> WHERE DATE_FORMAT(customer_date,'%a') = 'Fri'
> ORDER BY customer_date ASC;
> --
> Rik Wasmus
> ...spamrun finished

Rik,

I did forget to ask, this is being done within a PHP script....so, can
I put all that into a query and select the data? mysql_error() seems
not to like it.....

On Tue, 10 Jun 2008 17:39:29 +0200, Mtek <mtek@mtekusa.com> wrote:

> On Jun 10, 9:56 am, Rik Wasmus <luiheidsgoe...@hotmail.com> wrote:
>> Mtek wrote:
>> > Hi,
>>
>> > I'm trying to do a very convoluted query. I want to add subtract
>> > values in pairs while selecting records and display the results in the
>> > query like this:
>>
>> > SELECT customer_count, DATE_FORMAT(customer_date,'%a') dow,
>> > DATE_FORMAT(customer_date,'%c/%e') fmt_date FROM cust_stats WHERE
>> > DATE_FORMAT(customer_date,'%a') = 'Fri' ORDER BY customer_date ASC;
>>
>> > What I want to do, is for customer_count, I want to subtract the value
>> > from the previous record value and get the result in the query.
>>
>> > So, say I have these records (for name sake)
>>
>> > John Doe | 14 | 2008-03-14
>> > Jane Doe | 8 | 2008-04-01
>> > Jack Doe | 12 | 2008-05-19
>> > Jim Doe | 11 | 2008-06-10
>>
>> > I want to subtract Jane Doe from John Doe and get the result in the
>> > query result. (-6)
>> > I want to subtract Jack Doe from Jane Doe and get the result in the
>> > query result. (4)
>> > I want to subtract Jim Doe from Jack Doe and get the result in the
>> > query result. (-1)
>>
>> SET @previous = 0;
>> SELECT
>> customer_count - @previous as 'difference',
>> @previous := customer_count as 'customer_count',
>> DATE_FORMAT(customer_date,'%a') dow,
>> DATE_FORMAT(customer_date,'%c/%e') fmt_date
>> FROM cust_stats
>> WHERE DATE_FORMAT(customer_date,'%a') = 'Fri'
>> ORDER BY customer_date ASC;
>
>
> Very slick Rik. I've never seen some of those things, like
> @previous. I'm guessing some array?
>

Those are user defined variables, see
http://dev.mysql.com/doc/refman/5.0/...variables.html Keep in mind
those aren't portable to other databases.
--
Rik Wasmus
....spamrun finished