php - 使一列数据唯一并相加

Question

我在下面有一个工作表数据库：

id |                       market                       |  process   | lines | items 
----+----------------------------------------------------+------------+-------+-------
  1 | Spain                                              | Pick       |    25 |   100
  3 | UK                                                 | Pick       |   100 |   100
  4 | USA                                                | Pick       |    75 |    90
  5 | Metz                                               | Pick       |    10 |    20
  6 | UK                                                 | Pick       |    76 |    90
  7 | ROW                                                | Pick       |   200 |   300
  8 | Russia                                             | Pick       |   150 |  1000
  9 | South Africa                                       | Pick       |    35 |    50
 10 | Uk                                                 | Pick       |    15 |    20
 11 | USA                                                | Pick       |   123 |   123
 12 | Spain                                              | Pack       |    25 |    25
 13 | Spain                                              | Pack       |    50 |   100
 14 | UK                                                 | Pack       |   100 |   100
 15 | USA                                                | Pack       |    75 |    90
 16 | Metz                                               | Pack       |    10 |    20
 17 | UK                                                 | Pack       |    76 |    90
 18 | ROW                                                | Pack       |   200 |   300
 19 | Russia                                             | Pack       |   150 |  1000
 20 | South Africa                                       | Pack       |    35 |    50
 21 | Uk                                                 | Pack       |    15 |    20
 22 | USA                                                | Pack       |   123 |   123
  2 | Spain                                              | Pick       |    90 |   100

我在 php 中创建了一个 pg_query，如果在流程列中输入了 Pick，它会显示所有市场和行。这显示在 HTML 表格中。代码如下：

<?php 
  $db = pg_connect("host=localhost dbname=vcbv2 user=postgres");
  $result = pg_query($db, "SELECT * FROM jobs WHERE process = 'Pick'");
  echo "<table border='1'>";
        echo "<tr><td>MARKET</td><td>PICK LINES TO DO</td></tr>\n";
            while ($row = pg_fetch_assoc($result))
             {
              echo"<tr><td>{$row['market']}</td><td>{$row['lines']}</td></tr>\n";
             }
  echo"</table>";
?>

这将显示以下内容：

                       market                       | lines 
----------------------------------------------------+-------
 Spain                                              |    25
 UK                                                 |   100
 USA                                                |    75
 Metz                                               |    10
 UK                                                 |    76
 ROW                                                |   200
 Russia                                             |   150
 South Africa                                       |    35
 Uk                                                 |    15
 USA                                                |   123
 Spain                                              |    90

正如您在市场上看到的那样，有多个同名工作，即西班牙。

有没有办法让我只显示一次名称，并将条目的行加起来并显示为总数。如下所示：

                       market                       | lines 
----------------------------------------------------+-------
 Spain                                              |   115
 UK                                                 |   191
 USA                                                |   198
 Metz                                               |    10
 ROW                                                |   200
 Russia                                             |   150
 South Africa                                       |    35

我不希望从作业表中删除任何条目，因为我需要这些条目来做其他事情。

这可能吗？

标签： phphtmlpostgresql

Answer 1

Select market ,sum(lines) as lines_sum
from jobs WHERE process = 'Pick'
GROUP BY market