php mysql sum

php mysql sum


Create bellow table for all the lessons of mysql calculations.Here we will be discussed the function SUM().

Codes to create above table is bellow copy it and past in note pad and save it as .txt file then it could be imported to your my_database.

CREATE TABLE IF NOT EXISTS `computer_sale` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `computer_brand` varchar(40) NOT NULL,
  `annual_target` varchar(40) NOT NULL,
  `production` varchar(40) NOT NULL,
  `sale` varchar(40) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `computer_sale`
--

INSERT INTO `computer_sale` (`id`, `computer_brand`, `annual_target`, `production`, `sale`) VALUES
(1, 'Acer', '4000', '200', '100'),
(2, 'Dell', '5000', '500', '250'),
(3, 'Lenova', '1000', '100', '50'),
(4, 'HP', '5000', '2000', '1000'),
(5, 'IBM', '4000', '2000', '500');

Bellow PHP File shows you the sum of Annual Targets,Products And Sale Fields,

<?php $host="localhost"; $username="root"; $password=""; $db_name="my_database"; $tbl_name="computer_sale"; mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); $sql="SELECT * FROM $tbl_name"; $result=mysql_query($sql); ?> <table  align="center"  bgcolor="#15475c"> <tr bgcolor="#ddeef5"> <td align="center" ><strong>ID</strong></td> <td align="center" "><strong>Computer Brand</strong></td> <td  align="center" "><strong>Annual Target</strong></td> <td align="center" "><strong>production</strong></td> <td " align="center" "><strong>Sale</strong></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr bgcolor="#ddeef5"> <td bgcolor="#FFFFFF"><?php echo $rows['id']; ?></td> <td bgcolor="#FFFFFF"><?php echo $rows['computer_brand']; ?></td> <td align="center" bgcolor="#FFFFFF"><?php echo $rows['annual_target']; ?></td> <td align="center" bgcolor="#FFFFFF"><?php echo $rows['production']; ?></td> <td align="center" bgcolor="#FFFFFF"><?php echo $rows['sale']; ?></td> </tr> <?php } mysql_close(); ?> <?php mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); $query = "SELECT computer_brand,SUM(annual_target), SUM(production) , SUM(sale) FROM computer_sale";      $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_array($result)){ ?> <tr bgcolor="#ddeef5" align="center"><td colspan=2>Total</td><td><?php echo $row['SUM(annual_target)'];?></td><td><?php echo $row['SUM(production)'];?></td><td><?php echo $row['SUM(sale)'];?></td> </tr> <?php } mysql_close(); ?> </table>

Final display looks like bellow.

ID	Computer Brand	Annual Target	production	Sale
1	Acer	4000	200	100
2	Dell	5000	500	250
3	Lenova	1000	100	50
4	HP	5000	2000	1000
5	IBM	4000	2000	500
Total		19000	4800	1900