Advanced Group By help needed!

netfrugal

I'm trying to add a few more numbers to the columns in this pic.

[ATTACH]3113[/ATTACH]

Here is the PHP and query I am attempting:

<table>
  <tr>
    <td>Daily Report : </td>
    <td>Export</td>
    <td>Transactions # </td>
    <td>$ Amount </td>
    <td>Successful:</td>
    <td>Invalids:</td>
    <td>Declines</td>
  </tr>
 <?
 $query = "
SELECT dateCreated, count(recID) as number, sum(amount) as sales
FROM transactions
WHERE dateCreated BETWEEN '$startdate' AND '$enddate'
AND program_id = '$program_id'
GROUP BY dateCreated
ORDER BY dateCreated 
"; 

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
$dates = $row['dateCreated'];
$number = $row['number'];
$sales = round($row['sales'],2);
?>
  <tr>
    <td><? echo $dates; ?></td>
    <td><img src="../images/excel_logo.jpg"></td>
    <td><? echo $number; ?></td>
    <td><? echo money_format('%i', $sales); ?></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
<? } ?> 
</table>

As you can see, I am trying to populate the other columns. The column that determines whether they are Successful, Invalids, or Declines is the column: "response".

response = 1 (successful)
response = 2 (declines)
response = 3 (invalids)

So, that's what I am trying to do. Any help would be soooo appreciated!

cgraz

You could just grab responses, then use PHP to determine what to output to the user.

switch( $row['response'] )
{
	case 1:
		$output = 'Successful';
		break;

case 2:
	$output = 'Declined';
	break;

case 3:
	$output = 'Invalid';
	break;
}

print $output;

Or use a query like this (Note: untested)

SELECT dateCreated, 
   COUNT(recID) AS number, 
   SUM(amount) AS sales,
   CASE 
      WHEN response = 1 THEN 'successful' 
      WHEN response = 2 THEN 'declined' 
      WHEN response = 3 THEN 'invalid' 
   END
      AS response 
FROM transactions
WHERE 
      dateCreated BETWEEN '$startdate' AND '$enddate'
   AND program_id = '$program_id'
GROUP BY dateCreated 
ORDER BY dateCreated

netfrugal

Sorry, I wasn't clear, my fault.

I want to populate those columns with the numbers of totaled Successful transactions, totaled Declined transactions, and totaled invalid transactions.

I also wanted to tally the amounts together to get a lump sum of the dollar amounts for those columns too.

Here's an example: [ATTACH]3114[/ATTACH]

You'll see that the columns break down the amounts.

The column that says "Transactions #" is the total number of transactions for that particular day. This includes successful, declines, and invalids.

The next columns over I wish to break it down, so I can see how many successfuls, invalids, declines there are.

Is this better?

cgraz

Can you post your table structure?

netfrugal

table structure:

recID ( unique identifier for the rows)
program_id ( the id of the product or campaign)
response (this is from the gateway. It is either 1,2, or 3)
amount (the price of the product that was purchased)
dateCreated (when the purchased occured.)

There are plenty more columns, but these are the ones I believe are needed for this query.

thanks!

Roger_Ramjet

 $query = "
SELECT dateCreated, response, count(recID) as number, sum(amount) as sales
FROM transactions
WHERE dateCreated BETWEEN '$startdate' AND '$enddate'
AND program_id = '$program_id'
GROUP BY dateCreated, response
ORDER BY dateCreated, response
";

This will break the totals and counts up from each of your existing rows into a row for each response typr for that date. You will then have to do a running total for each row from the sub-totals for each response type. Remember if a date has no transactions for a response type then the row will be missing.