问题描述
你能帮帮我吗？如果您能提供解释的答案，我将更加感激，因为我想了解更多，实际上我已经为这个社区感到感激。对学习有很大帮助。但是只有答案对我来说已经足够了，因为我真的需要代码。
好，现在我来定义问题。
现在，假设我有带有这些列的表。
tbl_transaction-> id |参考|描述|价值|类别|子类别
value是整数列类型，具有一定数量的值。例如100000或200000。
类别和子类别是VARCHAR类型列，其中包含类别和子类别。
我想要的输出是它将显示基于其类别的价值总和。对于该逻辑，我尝试了该查询（它不是有效的mySQL代码标准，仅想显示一个逻辑）。
X =从tbl_transaction中选择DISTINCT类别；
从tbl_transaction WHERE category ='X'中选择SUM（value）；
它实际上在MySQL上运行良好。但我也想将此结果打印到PHP。问题是我无法与SUM和DISTINCT类别一起打印此值。我想要类似的东西（在PHP输出/ echo中）：
类别的总和
A | 100000
B | 400000
C | 500000
我在php上尝试了这些代码，它只能很好地打印类别，而不是类别的总和。
$ query1 = mysql_query（从tbl_transaction中选择DISTINCT类别）；
echo< table> tr< td>类别/ td>；
while（$ query2 = mysql_fetch_array（$ query1））
{
echo< tr>< td>。$ query2 ['category']。< / td> < / tr>< / table>；
$ query3 = mysql_query（从tbl_transaction中选择SUM（值）WHERE category =（从tbl_transaction中选择DISTINCT类别））;
echo类别/ td的< table> tr< td> SUM；
while（$ query4 = mysql_fetch_array（$ query3））
{
echo< tr>< td>。$ query4 ['value']。< / td> < / tr>< / table>；
}
}
我尝试了几次，结果为null /资源ID＃7 /错误。
类别A
子类别A-总金额子类别A的数量
子类别B-子类别B的总金额
子类别C-总额子类别C的数量
总计-类别A的总金额
类别B
子类别A-子类别A的总金额
子类别B-子类别B的总金额
子类别C-子类别C的总金额
总计-子类别C的总金额
在此先感谢您，Stackoverflow社区。
您可以尝试这样的sql语句来对按列分组的值求和：
选择SUM（value）作为Total_val，类别从tbl_transaction GROUP BY类别
您可以像这样在PHP中使用它：
（我将使用PDO键入，因为mysql_命令现在已经老了，不建议使用）
//创建连接
$ connection = new PDO（ mysql：host = 127.0.0.1; dbname = database_name， root， ）;
//创建主查询，获取主类别
$ query = $ connection-> prepare（ SELECT category，SUM（value）as MAIN_CAT_TOTAL_VAL FROM tbl_transaction GROUP BY category）;
$ query-> execute（）; //执行查询
foreach（$ query-> fetchAll（）as $ q）//获取结果
{
echo< b>。$ q [ category]。：< br /< / b>； //打印主要类别名称
//创建第二个查询，以子类别
$ query2 = $ connection-> prepare（ SELECT SUM（value）as TOTAL_VALUE ，子类别FROM tbl_transaction WHERE category =：p1 GROUP BY子类别）;
$ query2-> bindParam（：p1，$ q [ category]，PDO :: PARAM_STR）; //将参数绑定到第二个查询
$ query2-> execute（）; //执行第二个查询
foreach（$ query2-> fetchAll（）as $ q2）//获取结果
{
echo $ q2 [ sub-category]。- >。$ q2 [ TOTAL_VALUE]。< br />; //打印结果
}
echo类别。$ q [ category]的总数。 =。$ q [ MAIN_CAT_TOTAL_VAL];
echo< hr />; // cosmetics
}
如果您对其进行了正确的编辑，这应该可以工作。如果您不确定如何使用PDO，它可能看起来有些复杂，建议您看一下。
Could you help me out ? If you can provide answer with an explanation i will be more thankful because i want to learn more, i already thankful for this community actually. Helps me a lot with learning. But only answer is good enough for me since i really need the code for this.
Ok, now I will define the problem.
Now, lets say i have table with these column.tbl_transaction -> id | ref | description | value | category | sub-category
value is an integer column type, with some amount of value. like 100000 or 200000 for example.
category and subcategory is VARCHAR type column which contain category and subcategory.
my desired output is it will show SUM of value based on its category. for that logic i tried this query (its not a valid mySQL code standard, just want to show a logic).
X = select DISTINCT category from tbl_transaction;
select SUM(value) from tbl_transaction WHERE category='X';
its actually work well on MySQL. but i want to print this result to PHP too. the problem is i cant print this value along with SUM and DISTINCT category. i want something like (in PHP output / echo) :
Category | SUM of Category
A | 100000
B | 400000
C | 500000
i tried these code on php, and its only print the category well, but not the SUM of category.
$query1=mysql_query("select DISTINCT category from tbl_transaction");
echo "<table><tr><td>Category</td>";
while($query2=mysql_fetch_array($query1))
{
echo "<tr><td>".$query2['category']."</td></tr></table>";
$query3=mysql_query("select SUM(value) from tbl_transaction WHERE category ="(select DISTINCT category from tbl_transaction)"");
echo "<table><tr><td>SUM of Category</td>";
while($query4=mysql_fetch_array($query3))
{
echo "<tr><td>".$query4['value']."</td></tr></table>";
}
}
i tried several time, it results is null / resource id #7 / error. far more from this, i actually want something like this.
Category A
Sub Category A - Total Amount of Sub Category A
Sub Category B - Total Amount of Sub Category B
Sub Category C - Total Amount of Sub Category C
Total - Total Amount of Category A
Category B
Sub Category A - Total Amount of Sub Category A
Sub Category B - Total Amount of Sub Category B
Sub Category C - Total Amount of Sub Category C
Total - Total Amount of Category B
Thank you in advance, Stackoverflow community.
You can try a sql statement like this to SUM the values GROUPing by a column:
SELECT SUM(value) as Total_val, category FROM tbl_transaction GROUP BY category
And you can use this in PHP like this:(I'll type with PDO because mysql_ commands are getting old right now and not recommended)
//Creating connection
$connection=new PDO("mysql:host=127.0.0.1;dbname=database_name","root","");
//Creating main query, fetching main categories
$query=$connection->prepare("SELECT category, SUM(value) as MAIN_CAT_TOTAL_VAL FROM tbl_transaction GROUP BY category");
$query->execute(); //executing query
foreach($query->fetchAll() as $q) //fetching results
{
echo "<b>".$q["category"].":<br/></b>"; //printing main category name
//creating second query which sums value column and groups by sub category
$query2=$connection->prepare("SELECT SUM(value) as TOTAL_VALUE, sub-category FROM tbl_transaction WHERE category=:p1 GROUP BY sub-category");
$query2->bindParam(":p1",$q["category"],PDO::PARAM_STR); //binding parameter to second query
$query2->execute(); //executing second query
foreach($query2->fetchAll() as $q2) //fetching the results
{
echo $q2["sub-category"]." -> ".$q2["TOTAL_VALUE"]."<br/>"; //printing the results
}
echo "total amount of category ".$q["category"]." = ".$q["MAIN_CAT_TOTAL_VAL"];
echo "<hr/>"; //cosmetics
}
This should work if you edit it properly. It can look a little complicated if you're not sure how to use PDO, I suggest you to take a look at it.
这篇关于PHP& MySQL：显示某些事物的总和，按不同类别分类的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！