CODEWITHSUNDEEP
phpmysqlsum
pero furid
pero furid

Reputation: 17

How to show all fields while SUM them and show?

I have a problem with showing all names that are grouped by, for example:

This is my table:

IMEPRIIMEK  REZULTAT  EKIPA
------------------------------
Ela         256       e13e01
Joe         218       e13e01
Tim         198       e13e01
Dan         265       e13e02
Jim         256       e13e02
Kim         215       e13e02

With my code that I have made it I get this result:

#  EKIPA  TOTAL  IMEPRIIMEK
-------------------------------
                 Dan
1  e13e02  736   Jim
                 Kim
------------------------
                 Ela
2  e13e01  672   Joe 
                 Tim

Here is my code:

<?php

$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("paradox", $con);

$result2 = mysql_query("SELECT NazivEkipe, ImePriimek, Rezultat,
                        SUM(Rezultat) AS 'total'
                        FROM tekmovanje
                        WHERE NazivEkipe='e13e01' OR NazivEkipe='e13e02' OR NazivEkipe='e13e03' OR NazivEkipe='e13e04' OR NazivEkipe='e13e05'
                        GROUP BY NazivEkipe
                        ORDER BY total DESC") or die(mysql_error());
$rank = 0;
while($row = mysql_fetch_array ($result2))
{
$rank++; 
?>

<tr align="center" valign="middle">
     <td rowspan="3" align="center" valign="middle"><?php echo $rank; ?></td>

     <td rowspan="3" align="center" valign="middle" style="font-weight: bold; color: #008AFF;"><?php echo $row['total']; ?></td>

     <td rowspan="3" align="center" valign="middle"><?php echo $row['NazivEkipe']; ?></td>
<tr align="center" valign="middle">
  <td align="center" valign="middle" ><?php echo $row['ImePriimek']; ?></td>
</tr>

<?php
}
?>

</table>

Help me, thank you for your answers.

PS: This is for personal usage and it will not go online, so I don't care about SQL injection or doing this with PDO MySQLi

Upvotes: 0

Views: 113

Answers (3)

echo_Me
echo_Me

Reputation: 37233

you are missing the table tag <table>

try this

   SELECT Ekipa , ImePriimek, Rezultat,
                    SUM(Rezultat) AS total
                    FROM tekmovanje
                    WHERE Ekipa in ('e13e01','e13e02','e13e03','e13e04','e13e05')
                    GROUP BY Ekipa 
                    ORDER BY total DESC

DEMO HERE

or this if you will group by ImePriimek to show all users

   SELECT Ekipa , ImePriimek, Rezultat,
                    SUM(Rezultat) AS total
                    FROM tekmovanje
                    WHERE Ekipa in ('e13e01','e13e02','e13e03','e13e04','e13e05')
                    GROUP BY Ekipa ,ImePriimek
                    ORDER BY total DESC

DEMO HERE

EDIT .i think this is what you want get

try this

    SELECT Ekipa , group_concat(ImePriimek) as IMEPRIIMEK, Rezultat,
                    sum(Rezultat) AS total
                    FROM tekmovanje
                    WHERE Ekipa in ('e13e01','e13e02','e13e03','e13e04','e13e05')
                    GROUP BY Ekipa
                    ORDER BY total DESC

RESULT

   _______________________________________
  |  EKIPA  IMEPRIIMEK    REZULTAT TOTAL  |
  | ______________________________________|
  | e13e02  Dan,Jim,Kim     265     726   |
  | e13e01  Ela,Joe,Tim     256     672   |
  |_______________________________________|

look demo here

Upvotes: 1

John Woo
John Woo

Reputation: 263733

I think you want to get the totalResult for every EKIPA.

SELECT  a.*, b.totalResult
FROM    tableName a
        INNER JOIN
        (
            SELECT  EKIPA, SUM(REZULTAT) totalResult
            FROM    tableName 
            GROUP   BY EKIPA
        ) b ON a.EKIPA = b.EKIPA

Result

╔════════════╦══════════╦════════╦═════════════╗
║ IMEPRIIMEK ║ REZULTAT ║ EKIPA  ║ TOTALRESULT ║
╠════════════╬══════════╬════════╬═════════════╣
║ Ela        ║      256 ║ e13e01 ║         672 ║
║ Joe        ║      218 ║ e13e01 ║         672 ║
║ Tim        ║      198 ║ e13e01 ║         672 ║
║ Dan        ║      265 ║ e13e02 ║         736 ║
║ Jim        ║      256 ║ e13e02 ║         736 ║
║ Kim        ║      215 ║ e13e02 ║         736 ║
╚════════════╩══════════╩════════╩═════════════╝

Upvotes: 0

fthiella
fthiella

Reputation: 49049

Why not just this query?

SELECT EKIPA, sum(REZULTAT), GROUP_CONCAT(DISTINCT IMEPRIIMEK)
FROM yourtable
GROUP BY EKIPA

Upvotes: 0

Related Questions