All Forums Database
CCSlice 36 posts Joined 03/14
20 Jun 2014
Adding Values across multiple columns

Hello,
 Is it possible to have values for Teams added across columns?  Please look at the table below:
<!DOCTYPE html>
<html>
<head>
<style>
table,th,td
{
border:1px solid black;
border-collapse:collapse;
}
th,td
{
padding:5px;
}
</style>
</head>
<body>
<table style="width:300px">
<tr>
  <th>OriginTeam</th>
  <th>Operator</th> 
  <th>Origin_Count</th>
  <th> Area </th>
  <th> Type </th>
  <th> DestinTeam </th>
  <th> Operator </th>
  <th> Destin_Count </th>
</tr>
<tr>
  <td>TEAM002</td>
  <td>Stan Joseph</td> 
  <td>1</td>
  <td>North</td>
  <td>Warning </td>
  <td>TEAM001 </td>
  <td>Winnie Cheung </td>
  <td>1</td>
</tr>
<tr>
  <td>TEAM007</td>
  <td>Bobby Darin</td> 
  <td>0</td>
  <td>South</td>
  <td>File </td>
  <td>TEAM002 </td>
  <td>Stan Joseph </td>
  <td>1 </td>
</tr>
<tr>
  <td>TEAM004</td>
  <td>Wilma Flintstone</td> 
  <td>1</td>
  <td>West</td>
  <td>Warning</td>
  <td>TEAM009 </td>
  <td>Tom Thompson</td>
  <td>1</td> 
</tr>
<tr>
  <td>TEAM009</td>
  <td>Elaine Benes</td> 
  <td>1</td>
  <td>East</td>
  <td>Warning</td>
  <td>TEAM001</td>
  <td>Joseph Akinawa</td>
  <td>1</td> 
</tr>
<tr>
  <td>TEAM006</td>
  <td>Lloyd Braun</td> 
  <td>0</td>
  <td>East</td>
  <td>File</td>
  <td>TEAM007</td>
  <td>Bobby Darin</td>
  <td>1</td> 
</tr>
<tr>
  <td>TEAM001</td>
  <td>Joseph Akinawa</td> 
  <td>1</td>
  <td>East</td>
  <td>Warning</td>
  <td>TEAM002</td>
  <td>Molly Reins</td>
  <td>1</td> 
</tr>
<tr>
  <td>TEAM003</td>
  <td>Thomas Cook</td> 
  <td>0</td>
  <td>West</td>
  <td>File</td>
  <td>TEAM008</td>
  <td>Gloria Copes</td>
  <td>1</td> 
</tr>
</table>
</body>
</html>
 
To yield these results:
<!DOCTYPE html>
<html>
<head>
<style>
table,th,td
{
border:1px solid black;
border-collapse:collapse;
}
th,td
{
padding:5px;
}
</style>
</head>
<body>
<table style="width:300px">
<tr>
  <th>Team</th>
  <th>Operator</th> 
  <th>Total Count</th>
</tr>
<tr>
  <td>TEAM001</td>
  <td>Joseph Akinawa</td> 
  <td>2</td>
</tr>
<tr>
  <td>TEAM001</td>
  <td>Winnie Cheung</td> 
  <td>1</td>
</tr>
<tr>
  <td>TEAM002</td>
  <td>Stan Joseph</td> 
  <td>2</td>
</tr>
<tr>
  <td>TEAM004</td>
  <td>Wilma Flintstone</td> 
  <td>1</td>
</tr>
<tr>
  <td>TEAM007</td>
  <td>Bobby Darin</td> 
  <td>1</td>
</tr>
<tr>
  <td>TEAM008</td>
  <td>Gloria Copes</td> 
  <td>1</td>
</tr>
<tr>
  <td>TEAM009</td>
  <td>Elaine Benes</td> 
  <td>1</td>
</tr>
<tr>
  <td>TEAM009</td>
  <td>Tom Thompson</td> 
  <td>1</td>
</tr>
</table>
</body>
</html>
 
 

Raja_KT 1246 posts Joined 07/09
20 Jun 2014

Maybe you can think of writing in Javascript itself since it is html. There you have lots of options: functions, branching, looping etc... You have the logic at hands.
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Fred 1096 posts Joined 08/04
24 Jun 2014

You can't have two columns named Operator in the same SQL table. But it looks like what you mean by "sum across" would be the following:
SELECT Team, Operator, SUM(The_Count) as Total_Count FROM
(SELECT OriginTeam, Origin_Operator, Origin_Count FROM my_Table
  UNION ALL
 SELECT DestinTeam, Destin_Operator, Destin_Count  FROM my_Table
) as X(Team, Operator, The_Count)
GROUP BY Team, Operator
ORDER BY Team;

You must sign in to leave a comment.