iT邦幫忙

0

如何將同一天的筆數相加?印成正確的json

這是php查詢代碼

我的瀏覽次數統計是一個瀏覽就是一個數據筆

SELECT
FROM_UNIXTIME(add_time, '%Y-%m-%d') as add_time,
count(view_id) as viewTime
FROM `view`
GROUP BY `add_time`

這是整個php代碼

header('Content-type: application/json');
    $chartData = $pdo->query(
      "SELECT
FROM_UNIXTIME(add_time, '%Y-%m-%d') as add_time,
count(view_id) as viewTime
FROM `view`
GROUP BY `add_time` "
    );

    $data = array();
    $key = 0;
    foreach ($chartData as $item){
      $data[$key] = array(
        'date' => $item['add_time'],
        'number' => $item['viewTime']
      );

      $key++;
    }
    echo json_encode($data);

打印結果

[{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"},{"date":"2018-08-16","number":"1"}.............

右邊的 viewTime 都是一筆數據
但我想實現的是
日期重複只要顯示一個
但是同一個日期的數字都相加
這能怎麼實現?
我要在後端打印出這樣的json
可以的話跟我說一下原理~感謝

froce iT邦大師 1 級 ‧ 2018-09-04 16:02:19 檢舉
可以吐槽一下嗎?
你viewTime那個欄位根本沒作用啊。
直接count同一日出現次數就行了。
火爆浪子 iT邦研究生 1 級 ‧ 2018-09-05 10:03:20 檢舉
疑?但我 viewTime 是要給array的 number 耶
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
3
純真的人
iT邦大師 1 級 ‧ 2018-09-04 15:31:11
最佳解答

你SQL再做一次查詢~就複合加總了~

select add_time
,sum(viewTime) as viewTime
from (
    SELECT
    FROM_UNIXTIME(add_time, '%Y-%m-%d') as add_time,
    count(view_id) as viewTime
    FROM `view`
    GROUP BY `add_time` 
) as k
group by add_time
0
小魚
iT邦大師 1 級 ‧ 2018-09-04 19:35:32

我怎麼覺得怪怪的...
你的語法跟你的圖連不起來 @@
我直接看你的圖回答,
其實直接SUM就可以了...

SELECT `date`, SUM(viewTime) AS viewTime 
FROM `view` 
GROUP BY `date`
火爆浪子 iT邦研究生 1 級 ‧ 2018-09-05 10:01:54 檢舉

喔對,我date 是 add_time 我上面是直接查詢,改了欄位名 哈哈

1
犬千賀
iT邦新手 3 級 ‧ 2018-09-07 11:21:47

幾位大大沒看懂你的問題啦...
你應該要把有view_id, add_time的table給我們看,而不是result...(用錯的東西回推嗎?

我大概猜一下你的add_time應該是DateTime格式
FROM_UNIXTIME 只是"顯示"成YYYY-MM-DD,但其實是UnixTime,所以GROUP沒用~
可以改成這樣 GROUP BY DATE(time)

我要發表回答

立即登入回答