dongmi4734 2013-06-23 17:50
浏览 59
已采纳

当项在指定列中具有相同值时合并SQL数据库值

I know how to solve this in a non-elegant, non-practical way, meaning sending requests, one by one, for each value in specified column that is shared and then handle all data from that specific output, then, do the same for another value, etc., etc. But I was wondering if someone out there thought of an ELEGANT and PRACTICAL way, which would mean all this is handled by one single PHP function.

Basically, I have this table:

  location  |   title   | description | quantity 
============|===========|=============|==========
  shelf     |   apple   |    red      |    2
  drawer    |   banana  |    yellow   |    4
  shelf     |   kiwi    |    green    |    2
  cupboard  |   lemon   |    yellow   |    1
  fridge    |   melon   |    orange   |    3
  drawer    |   peach   |    orange   |    1

And what I eventually want is to create a jQuery pie chart that tells me what percentage of title is in each location. But before that I need a function that outputs shelf => 4 (2+2), drawer => 5 (4+1), etc., etc.

So, the question is, is there an elegant, practical way to make this happen? Meaning, to retrieve all rows but group together all data by location and, then, sum each location's quantity to be later on turned into a percentage for the pie chart?

Thanks!

  • 写回答

2条回答 默认 最新

  • dongsheng8158 2013-06-23 17:54
    关注
    select location, sum (quantity) as sum_quantity from table group by location;
    

    and later using $rows as result from query above,

    function prepareForChart ($rows) {
    
      $ret = array();
      $total = 0;
      foreach ($rows as $el) $total += $el["sum_quantity"];
    
      foreach ($rows as $el) {
        $ret[] = array(
          $el["location"] => $el["sum_quantity"],
          "percentage"    => 100 * $el["sum_quantity"]/$total,
        );
      }
    
      return $ret;
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题