[PHP] Column Totals
I am trying to get the total sales revenue for each day of the month, and
having no luck at all.
The following query seems to return an empty string, which should only give
me the total for the entire column anyway.
$query = SELECT SUM(Revenue) FROM Sales WHERE Date='$Date';
I also tried, among many, many things, to use a for loop to get the sum for
each date, month to date, such as:
$Today = date(d);
for ($i = 1; $i $Today; $i++) {
$query = SELECT SUM(Revenue) FROM Sales WHERE Date='$i';
}
In case it helps, sample table data might look like this:
OrderNoOrderDateRevenue
1 79.95
1112 1 39.95
1113 3 89.95
and I am trying to produce something that would show:
TABLE
TR
TDDate/TDTDRevenue/TD
/TR
TR
TD1/TDTD119.90/TD
/TR
TR
TD2/TDTD0.00/TD
/TR
TR
TD3/TDTD89.95/TD
/TR
/TABLE
PLEASE HELP. PLEASE!! And many, many thanks in advance
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Column Totals
[snip] The following query seems to return an empty string, which should only give me the total for the entire column anyway. $query = SELECT SUM(Revenue) FROM Sales WHERE Date='$Date'; [/snip] $query = SELECT SUM(Revenue) AS Revenue FROM Sales WHERE Date='$Date'; note the AS statement-^ $foo = mysql_query($query, $connection); $bar = mysql_fetch_array($foo); echo $bar['Revenue'] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Column Totals
-Original Message-
From: Robby Russell [mailto:[EMAIL PROTECTED]
Sent: Monday, November 15, 2004 11:12 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: [PHP] Column Totals
On Mon, 2004-11-15 at 11:01 -0700, Ben Miller wrote:
SELECT SUM(Revenue) FROM Sales WHERE Date='$Date'
What happens when you do:
SELECT Revenue FROM Sales WHERE Date='$Date';
Make sure you're getting values back first..and then you can try to sum
them.
-Robby
--
/***
* Robby Russell | Owner.Developer.Geek
* PLANET ARGON | www.planetargon.com
* Portland, OR | [EMAIL PROTECTED]
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting Development
*--- Now supporting PHP5 ---
/
I am getting values back in this case, if there are values for the given
date, that is. If I do:
while ($row = mysql_fetch_array($result)) {
extract($row);
echo TRTD$DateBR/TDTD$RevenueBR/TD/TR;
}
for example, it returns a long list of individual sales.
Ben
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Column Totals
Ben Miller wrote: I am trying to get the total sales revenue for each day of the month, and having no luck at all. snipped/ In case it helps, sample table data might look like this: OrderNoOrderDateRevenue 1 79.95 1112 1 39.95 1113 3 89.95 I would guess that you have a char type for your Revenue Column which would lead to this situation. If you are unsure about which data type to use you will have to ask in a mysql mailing list. you might also want to echo out your query and run it directly in the mysql client to see what happens. Liberal user of error_log(mysql_error()) in your code would probably help too. -- Raditha Dissanayake. -- http://www.radinks.com/print/card-designer/ | Card Designer Applet http://www.radinks.com/upload/ | Drag and Drop Upload -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Column Totals
That did it perfectly. Why don't any of the stupid books tell you that you need the AS statement? -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Monday, November 15, 2004 11:16 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [PHP] Column Totals [snip] The following query seems to return an empty string, which should only give me the total for the entire column anyway. $query = SELECT SUM(Revenue) FROM Sales WHERE Date='$Date'; [/snip] $query = SELECT SUM(Revenue) AS Revenue FROM Sales WHERE Date='$Date'; note the AS statement-^ $foo = mysql_query($query, $connection); $bar = mysql_fetch_array($foo); echo $bar['Revenue'] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Column Totals
On Mon, 15 Nov 2004 11:21:45 -0700, Ben Miller [EMAIL PROTECTED] wrote: That did it perfectly. Why don't any of the stupid books tell you that you need the AS statement? Because they assumed you would access it just like you asked for it. $bar['SUM(Revenue)'] -- Greg Donald Zend Certified Engineer http://gdconsultants.com/ http://destiney.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Column Totals
Because you don't technically 'need' it, unless you're trying to reference the results by column name and you're using an aggregate like that. :) And in most systems, you don't need the AS just a space and a new name: SELECT SUM(Revenue) Revenue FROM Sales Where. But all depends on your style. I don't think the SQL Server or Oracle server I deal with care if you put the AS in there or not. Again, it's not required unless you're relying on a name coming back with the result set, then you might want to be explict with an alias like that. Also, when you use aggregates, remember that they'll they group the non-aggregate functions based on uniqueness. This will happen even if you're not displaying the non-aggregate functions, so you may get repeats of some things that you can get rid of by using SELECT DISTINCT Sum(... But if there's anything non-unique in what you're using in SELECT or WHERE, it'll use that for grouping. This includes the hours, minutes and seconds in a date field sometimes. If the query below worked for you, then I guess it's enough for MySQL (is that what you're using?) but in some systems it may not be and you may get every row in the database non-SUM'd because the dates all have different seconds in the time. In that case, you'd need to section out the day/month/year and compare based on that or something. Just some pitfalls to watch out for when you're not used to aggregates and grouping. -TG -Original Message- From: Ben Miller [mailto:[EMAIL PROTECTED] Sent: Monday, November 15, 2004 1:22 PM To: [EMAIL PROTECTED] Subject: RE: [PHP] Column Totals That did it perfectly. Why don't any of the stupid books tell you that you need the AS statement? -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Monday, November 15, 2004 11:16 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [PHP] Column Totals [snip] The following query seems to return an empty string, which should only give me the total for the entire column anyway. $query = SELECT SUM(Revenue) FROM Sales WHERE Date='$Date'; [/snip] $query = SELECT SUM(Revenue) AS Revenue FROM Sales WHERE Date='$Date'; note the AS statement-^ $foo = mysql_query($query, $connection); $bar = mysql_fetch_array($foo); echo $bar['Revenue'] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Column Totals
On Tuesday 16 November 2004 02:21, Ben Miller wrote: That did it perfectly. Why don't any of the stupid books tell you that you need the AS statement? You don't *need* the AS. You just need to be aware that without the AS the query will return a column called 'SUM(columnname)'. The AS is for convenience and not mandatory. -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-general -- /* Earth -- mother of the most beautiful women in the universe. -- Apollo, Who Mourns for Adonais? stardate 3468.1 */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Column Totals
On Mon, 2004-11-15 at 11:01 -0700, Ben Miller wrote: SELECT SUM(Revenue) FROM Sales WHERE Date='$Date' What happens when you do: SELECT Revenue FROM Sales WHERE Date='$Date'; Make sure you're getting values back first..and then you can try to sum them. -Robby -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting Development *--- Now supporting PHP5 --- / signature.asc Description: This is a digitally signed message part
