SUM problem

[netfrugal]

I was correctly getting the SUM of my 'amount' column from my transactions table. But I was also only querying just the transactions table. Now I am trying to query 2 tables, but with the same results.

This is the query that works - it is querying only 1 table:
It will print a dollar amount like: $5.66

$sum = mysql_query("SELECT SUM(amount) FROM transactions WHERE type = 'sale' AND dateCreated BETWEEN '$startdate' AND '$enddate' AND product_id = '$product_id'");  

while($row = mysql_fetch_array($sum)){
$total = $row['SUM(amount)'];
}

echo money_format('%i', $total);

Now, this is the query that does not display the totaled amount, and it stopped working when I added the customers table in the query:

$sum = mysql_query("SELECT SUM(t.amount), c.* FROM transactions t , customers c WHERE t.customer_id = c.customer_id AND t.type = 'sale' AND c.misc2 = '2' AND t.dateCreated BETWEEN '$startdate' AND '$enddate' AND t.product_id = '$product_id'");  

while($row = mysql_fetch_array($sum)){
	$total = $row['SUM(amount)'];
}

echo money_format('%i', $total);

Am I using SUM wrong?

[Shrike]

When using aggregation functions such as SUM you need to use the GROUP BY clause on columns which are being displayed as is. In doing so you tell the database how to break down the result set. Try to imagine the result set if you didn't have any groupings - 1 row would contain the total sum, and 1+n rows would contain the other columns. It doesn't make sense.

[Piranha]

SUM is the sum for each row that would otherwise be returned. Since you want to return all rows from c you will not get the total, you will get the sum from t for each customerid.

Can you give an example of what you want, then it is easier to help you out.

[netfrugal]

I simply want to add all rows of "amount" together to get a totaled amount.

[nemonoman]

I simply want to add all rows of "amount" together to get a totaled amount.

Use your first query.

If you are trying to get the sum by customer (so much for cust A, so much for cust b...) add this to your query after AND t.product_id = '$product_id

GROUP BY t.customer_id

You'll get the customer's summed transaction.amount, and the c.* record.

[netfrugal]

I apologize, I'm not explaining everything proper. My fault.

I want to add all sales for a date range and get a dollar amount. The column that has all the dollar amounts is the "amount" column, located in the transactions table. I only need the customers table because a particular column (misc2) identifies specific customers I need to report on.

I thought my queries were adding all amounts from the transactions table - with the date range I specified. I'm not quite sure about the GROUP BY statement, and I may need a working example to see why I would need it.

Is this explained ok?

[cgraz]

I think you're after this:

GROUP BY t.customer_id WITH ROLLUP

That should give you the total as the last result if you're running MySQL >= 4.1

[netfrugal]

hmm, I must be missing something. This is what I put into the query:

SELECT SUM(t.amount), c.* FROM transactions t , customers c WHERE t.customer_id = c.customer_id AND t.type = 'sale' AND c.misc2 = '2' AND t.dateCreated BETWEEN '2007-05-29' AND '2007-05-30' AND t.product_id = '1' GROUP BY t.customer_id WITH ROLLUP

For my limited knowledge on how to display a lump sum amount, how would you write a simple query, and the echo statement to get the lump sum of all amounts in a database?

[netfrugal]

This is what I figured out:

$result = mysql_query("SELECT SUM(amount) AS lump FROM transactions");

$row = mysql_fetch_array( $result );

echo "amount: ".$row['lump'];

This gets me what I wanted, all amounts added together.

BUT!

When I try to add more to the query:

$result = mysql_query("SELECT SUM(t.amount) AS lump, c.* FROM transactions t, customer c");

$row = mysql_fetch_array( $result );

echo "amount: ".$row['lump'];

the $row['lump'] stops working completely.

What gives?