Sum of fields to graph

Question

Sum of fields to graph

Navigation

#1 by (0 votes)

0

I make a system that are basically work tickets with which one of the main objectives is to measure how many cases are prioritized (low, medium or high), my DB is designed with foreign keys so I need to graphing show me the sum of those lines with that priority, that is, add all those with priority 1, 2 or 3.

My system is already graphical but the problem I have is that it does not add according to the priority it only shows all the lenas and the title of the line, because I do not know how to make that sum and show me what I need to say that sume and that shows the graph (high, medium and low).

This is how my central table ticket was designed, I really hope you can help me.

id              PK
title
description
process_1   
process_2   
updated_at  
created_at  
kind_id         FK  
user_id         FK  
asigned_id  
project_id      FK
category_id     FK
priority_id     FK
turn_id         FK
area_id         FK
status_id       FK

My foreign table priority

id      PK  
name

Here is the code of my graphic

Here select the table and record the records with the titles according to the field that I select "title"

 <?php 
	$sql=mysql_query("select * from ticket order by priority_id desc");
    while ($res=mysql_fetch_array($sql)){
					  ?>
		
		
		['<?php echo $res['title']   ?>'],
		
		
		<?php
		}
		?>

This is where you select the priority_id field, This is where I need you to make the sum on that field, that is, if there are several "1" that add them up and assign it to "high" since it is in my key foreign high = 1

<?php 

               $sql=mysql_query("SELECT priority_id, COUNT(*) total FROM ticket GROUP BY priority_id");
                  while ($res=mysql_fetch_array($sql)){
                  ?>



                  [<?php echo $res['total']?> ],




                <?php
                }             
                ?>

php php5 graficas

asked by Carlos 16.06.2018 в 05:19

source

1 answer

Call getActionCommand with a KeyEvent Get value from a HashMap

score 0 · Accepted Answer

In this case, more than adding, I understand that it is about counting how many tickets there are with each of the priorities.

In these cases you can rely on the clauses COUNT and GROUP BY SQL.

COUNT, counts the total registration number, example:

SELECT COUNT(*) FROM ticket;

GROUP BY, group equal data by one or more columns, example:

SELECT * FROM ticket GROUP BY priority_id;

Using both we can count the number of records of each group, or what is the same, count the number of equal records for a given column. Example:

SELECT priority_id, COUNT(*) total
FROM ticket
GROUP BY priority_id;

With this we get the result you expect, knowing the total of records of each of the priorities.

If you also want to obtain the names by the foreign key, you can add an INNER JOIN, example:

SELECT t.priority_id, p.name, COUNT(*) total
FROM ticket t
INNER JOIN priority p ON t.priority_id = p.id
GROUP BY t.priority_id, p.name;

Apart from the indicated thing, letting you know that the mysql_ * extension of php is obsolete and was deleted in the PHP7 version, so its use is totally inadvisable, instead MySQLi or PDO should be used.