Group campaigns by first 3 characters 1

[jongag1]

Hello,

I have a query which shows several campaigns with their belonging information.

What I try to do is to display the grouped campaigns which start with the same 3 charactersers. Each campaign has a main id. These 3 characters can be different per main id.

Example of the fields which are in the mysql dbase:
id, agentnaam, maandag, dinsdag, ..., zondag, week, beltijd, campagne, datum, hoofd, type, jaaruur, mauur, diuur, ..., zouur, madeel, dideel, ...,zodeel

There can be more than 1 campaign connected to a main id (field: hoofd).
An example: gpavond, nrkdag, nrkavond are all connected to main id=9

With the below query I can see per campaign the data:
gpavond,5,12,48
nrkdag,6,8,12
nrkavond,3,3,34

Query:
echo "
<table border=0 cellspacing=0 cellpadding=2 class='CustomTable' width='100%'>
<tr class='lightpurple'>
<td><b>campagne</td>
<td><b>$type2</td>
<td><b>beltijd</td>
<td><b>uur$type</td>
</tr>";

$x=0;
$resultaat = mysql_query("SELECT week AS week, Sum(maandag+dinsdag+woensdag+donderdag+vrijdag+zaterdag+zondag) AS Totaal, Sum(beltijd) AS beltijd, campagne AS campagne FROM resultpdv WHERE (week='$weekid' AND hoofd=$id AND agentnaam='$agent') GROUP BY campagne");

while($record = mysql_fetch_object($resultaat))
{
if($x == "0") { $class = "darkpurple"; }
if($x == "1") { $class = "lightpurple"; }

echo "<tr class='$class'>
<td>".stripslashes($record->campagne)."</td>
<td>".stripslashes($record->Totaal)."</td>";

$totaal=($record->Totaal);
$beltijd=($record->beltijd);
$beltijd=round($beltijd,2);

echo "<td>$beltijd</td>";

$uuromzet = $totaal/$beltijd;
if ($type == "score") {
if ($uuromzet>0.7) {$kleur="green";}
else if ($uuromzet<0.5) {$kleur="red";}
else {$kleur="orange";}
}

else if ($type == "omzet") {
if ($uuromzet>40)
{$kleur="green";}
else if ($uuromzet<30)
{$kleur="red";}
else {$kleur="orange";}
}

echo "<td><font color=$kleur><b>";
echo round($uuromzet,2);
echo "</b></font></td></tr>";

$x++;
if($x == "2") { $x=0; }
}

echo "</table>";

What I would like to see is:
gpavond,5,12,48
nrk,9,11,45

The campaigns can change, it can be that next week there is a new campaign with the name gpdag.

Somebody suggested me to use HAVING, but I am not able to get it right.
Does somebody has an idea/suggestion how this can de done?

Thanks,
Age

 

[r937]

What I would like to see is:
gpavond,5,12,48
nrk,9,11,45

would you accept this instead --

gpa,5,12,48
nrk,9,11,45



r937.com | rudy.ca

 

[jongag1]

That is no problem!

 

[r937]

SELECT week
     , Sum(maandag+dinsdag+woensdag
          +donderdag+vrijdag+zaterdag
          +zondag) AS Totaal
     , Sum(beltijd) AS beltijd
     , [b]left(campagne,3) AS campagne3[/b]
  FROM resultpdv 
 WHERE week = '$weekid' 
   AND hoofd = $id 
   AND agentnaam = '$agent'
GROUP 
    BY week
     , [b]campagne3[/b]

r937.com | rudy.ca

 

[jongag1]

Works great!

Thanks,
Age