dongxu7408 2016-07-28 15:17
浏览 40
已采纳

PHP和MySQL:SUM值BY DISTINCT类别

thank you before for take an interest into my case. In short i want to tell that i wonder if Value in MySQL table can be counted using by just MySQL query or need PHP code to count. And i have no idea how to code it.

Case :

I have tbl_transaction which has format like this :

id | ref | description | value | date | category | sub_category

Value is an INT type column, for example it contains 10000, 20000, etc..

Let's say that i want to SUM value column, but by DISTINCT Category

So for example, it's going to work like this :

category A SUM minus(-) category B SUM plus(+) category C SUM = Result of Counted SUM

Experiment :

I tried something like this, obviously wont work but i try to show my logic for solve a case here.

SELECT SUM(value) as MAIN_CAT_TOTAL_VAL 
  FROM tbl_transaction 
 WHERE monthTransaction LIKE '$newmonthminusone%' ONE
     , ONE+3

Desired Learning :

I want to know how mySQL query can be printed into PHP. And code behind that if possible, i actually need the code. Some say that mysql_ function isnt really up-to-date anymore and shouldn't be used. But i have no bright knowledge on how to use new function that replace mysql_ function, like maybe PDO and some kind like that. I really need explanation. I learn from code and case, that's how i adapt at least. So code would be help me much.

Desired Output :

Number / value, result of SUMMED UP table calculation (math). 

I have no idea at all, how to code and start. Been stuck for hours. I sincerely need help for this. Ty so much.

  • 写回答

3条回答 默认 最新

  • duanpasi6287 2016-07-28 15:32
    关注

    mysqli_* is pretty much a drop-in replacement for mysql_*, and is a good transition to learning PDO, as you can use your (mostly) unmodified existing code, and transition toward the object-oriented approach and prepared statements as you learn about them.

    That said, what you are probably looking for in a query is something like:

    SELECT
        category,
        SUM(value) AS MAIN_CAT_TOTAL_VAL
    FROM tbl_transaction
    WHERE monthTransaction LIKE '$newmonthminusone%' AS ONE
    GROUP BY category;
    

    Then you'll be able to work with it like so:

    $link = mysqli_connect("localhost", "dbuser", "dbpassword", "dbname");
    $query = mysqli_query($link, $queryStringFromAbove);
    $total = 0;
    while($row = mysqli_fetch_assoc($query)) {
        switch($row['category']) {
            case "A":
            case "C":
                $total += $row['MAIN_CAT_TOTAL_VAL'];
                break;
            case "B":
                $total -= $row['MAIN_CAT_TOTAL_VAL'];
                break;
        }
    }
    echo $total;
    

    Edit: It's difficult to tell precisely your conditions of adding or subtracting, but the switch() block should be flexible enough to customize to your needs.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 MATLAB动图的问题
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名