MONTHLY TOTALS

[gunthertoody]

I'm a novice and am trying to get monthly totals for a specific column.

 

TABLE NAME:  customers_history

COLUMN NAME: status

 

If a value in the "status" column equals "1" "2" or "4" I need to count it for that month. Your help is greatly appreciated.

 

$result = mysql_query("SELECT status, MONTH(date) FROM customers_history WHERE status ='1' OR status = '2' OR status = '4' GROUP BY MONTH(date) ORDER BY date DESC LIMIT 12") or die(mysql_error());

	// Print out result
    $bg = '#ffffff'; //For Initial Row Color
    while($row = mysql_fetch_array($result)){
	$bg = ($bg=='#ffffff' ? '#e7e8e8' : '#ffffff'); //For Alternate Row Colors
   
	  echo "<tr bgcolor='{$bg}'>";
	  echo "<td><span class='rowWidth1'>";
	  echo $row['date'];
	  }
	  echo '</span></td></tr>';
	echo '</table>';
	echo '<br />';

[gunthertoody]

I think i'm getting closer. Below is the revised code. My monthly columns aren't adding correctly and the first month is not the latest. It's starting with May 2009 for some reason.

 

	//Create Variable for Cumulative Monthly Stats
	echo '<table class="newTable"><tr><th>MONTH</th><th>ADDED</th><th>CANCELLED</th><th>TOTAL</th></tr>';
     
	$result = mysql_query("SELECT date, COUNT(status) FROM customers_history WHERE status ='1' OR status = '2' OR status = '4' GROUP BY MONTH(date) ORDER BY date DESC LIMIT 25") or die(mysql_error());

	// Print out result for Cumulative Monthly Stats
    $bg = '#ffffff'; //For Initial Row Color
    while($row = mysql_fetch_array($result)){
	$bg = ($bg=='#ffffff' ? '#e7e8e8' : '#ffffff'); //For Alternate Row Colors
   
	  echo "<tr bgcolor='{$bg}'>";
	  echo "<td><span class='rowWidth1'>";
	  echo $row['date'];
	  echo "</span></td>";
	  echo "<td><span class='rowWidth1'>";
	  echo $row['COUNT(status)'];
	  echo "</span></td></tr>";
	  
	  }
	echo '</table>';
	echo '<br />';

[fenway]

Let's see some sample data.

[gunthertoody]

MONTH ADDED CANCELLED TOTAL

2009-05-01 105

2009-04-01 76

2009-03-02 90

2009-02-03 109

2009-01-01 168

2008-12-03 106

2008-11-03 100

2008-10-01 112

2008-09-01 127

2008-08-01 162

2008-07-01 131

2008-06-23 83

[fenway]

Is that input or output? You show 4 column headers but only 2 columns of data.

[gunthertoody]

it's my output. I haven't done the other two columns yet.

[kickstart]

Hi

 

One issue is that you are selecting different column than the ones you are grouping on. The actual date you get will be fairly random.

 

Not sure if you have rows with months newer than May 2009 with a status of 1, 2 or 4.

 

Something like this might do it (not tested)

 

SELECT MonthDate, COUNT(status) 
FROM (SELECT DISTINCT MONTH(date) AS MonthDate FROM customers_history) a
LEFT OUTER JOIN customers_history b
ON a.MonthDate = MONTH(b.date)
WHERE b.status ='1' OR b.status = '2' OR b.status = '4' 
GROUP BY MonthDate 
ORDER BY date DESC LIMIT 25

 

I assume that you don't really have a column called date.

 

All the best

 

Keith

[gunthertoody]

Sorry, but I should clarify as I didn't made it clear enough.

 

TABLE NAME:  customers_history

COLUMN NAME: status

COLUMN NAME: date

 

I need to count the number of times "1" or "2" or "4" appear in the "status" column for a given month.

 

IF I HAVE:

 

date                                              status

2011-02-25                                      4

2011-02-14                                      1

2011-02-03                                      0

2011-02-01                                      2

2011-01-21                                      1

2011-01-12                                      4

 

 

 

OUTPUT SHOULD BE:

 

Feb. 2011                                          3

Jan. 2011                                          2

 

(Note the zero isn't counted)

 

[fenway]

Why not a simply SUM( IF(expr), 1, 0)?

[gunthertoody]

I'm up for the easiest and clearest way to get it done, but I'm a complete novice and am not sure how that would work? If you could spell it out it would be greatly appreciated.

 

David

[kickstart]

Hi

 

Give this a try

 

SELECT DATE_FORMAT(`date`, '%Y %m'), COUNT(*) 
FROM customers_history 
WHERE status IN ('1', '2', '4') 
GROUP BY DATE_FORMAT(`date`, '%Y %m') 
ORDER BY DATE_FORMAT(`date`, '%Y %m') DESC LIMIT 12

 

All the best

 

Keith

[gunthertoody]

Thank for the help Keith. I tried to echo it out, but am not sure I'm doing it right as the screen is just blank...

 

  echo "<tr bgcolor='{$bg}'>";
	  echo "<td><span class='rowWidth1'>";
	  echo $row['DATE_FORMAT(date)'];
	  echo "</span></td>";
	  echo "<td><span class='rowWidth1'>";
	  echo $row['status'];
	  echo "</span></td></tr>";

[Muddy_Funster]

You have no row selected called status(and clearly no error reporting on either - which is never handy).

you would need to

echo $row['COUNT(*)'];

instead.

[kickstart]

Hi

 

Either use the column numbers or give the columns alias names and use those for outputting the data.

 

SELECT DATE_FORMAT(`date`, '%Y %m') AS YearMonth, COUNT(*)  AS StatusCount
FROM customers_history 
WHERE status IN ('1', '2', '4') 
GROUP BY DATE_FORMAT(`date`, '%Y %m') 
ORDER BY DATE_FORMAT(`date`, '%Y %m') DESC LIMIT 12 

 

All the best

 

Keith

[gunthertoody]

I must be missing something because i just get a blank screen. Here the entire code:

 

$result = mysql_query("SELECT DATE_FORMAT(`date`, '%Y %m'), COUNT(*) 
	FROM customers_history 
	WHERE status IN ('1', '2', '4') 
	GROUP BY DATE_FORMAT(`date`, '%Y %m') 
	ORDER BY DATE_FORMAT(`date`, '%Y %m') DESC LIMIT 12") or die(mysql_error());

	$bg = '#ffffff'; //For Initial Row Color
    while($row = mysql_fetch_array($result)){
	$bg = ($bg=='#ffffff' ? '#e7e8e8' : '#ffffff'); //For Alternate Row Colors
   
	  echo "<tr bgcolor='{$bg}'>";
	  echo "<td><span class='rowWidth1'>";
	  echo $row['COUNT(*)'];
	  echo "</span></td>";
	  echo "<td><span class='rowWidth1'>";
	  echo $row['status'];
	  echo "</span></td></tr>";

[Muddy_Funster]

you're still echoing $row['status'] when you don't actualy have a $row['status']. use this and see what happens:

echo "<tr bgcolor='{$bg}'>";
	  echo "<td><span class='rowWidth1'>";
	  echo $row[0];
	  echo "</span></td>";
	  echo "<td><span class='rowWidth1'>";
	  echo $row[1];
	  echo "</span></td></tr>";

[gunthertoody]

I used it and I got one solid string: 2011 02402011 01592010 12522010 11432010 10432010 09472010 08382010 07292010 06412010 05442010 04422010 0364

 

restate:

 

TABLE NAME:  customers_history

COLUMN NAME: status

COLUMN NAME: date

 

I need to count the number of times "1" or "2" or "4" appear in the "status" column for a given month.

 

IF I HAVE:

 

date                                              status

2011-02-25                                      4

2011-02-14                                      1

2011-02-03                                      0

2011-02-01                                      2

2011-01-21                                      1

2011-01-12                                      4

 

OUTPUT SHOULD BE:

 

Feb. 2011                                          3

Jan. 2011                                          2

 

 

Thank you for your patience.

 

David

 

[Muddy_Funster]

SELECT DATE_FORMAT(`date`, '%b %Y'), COUNT(*) 
FROM customers_history 
WHERE status IN ('1', '2', '4') 
GROUP BY DATE_FORMAT(`date`, '%b %Y') 
ORDER BY DATE_FORMAT(`date`, '%b %Y') DESC LIMIT 12

 

will get the dates looking how you want.  The rest of the formatiing is all to do with with your CSS and php.  The "all in one line" issue is because the table isn't being generated properly, as I just copied and pasted your code I didn't check the output formating, here, have a go with this:

$bg = '#ffffff'; //For Initial Row Color

echo '<table border="1">
while ($row = mysql_fetch_array($result)){
  $bg = ($bg=='#ffffff' ? '#e7e8e8' : '#ffffff'); //For Alternate Row Colors
  echo "<tr bgcolor='{$bg}'>";
  echo "<td><span class='rowWidth1'>";
  echo $row[0];
  echo "</span></td>";
  echo "<td><span class='rowWidth1'>";
  echo $row[1];
  echo "</span></td></tr>";
}
echo '</table>'