I need your help!!!.
I need to query a table in my database that has record of goods sold. I want the query to detect a particular product and also calculate the quantity sold. The product are 300 now, but it would increase in the future.
Below is a sample of my DB Table
#----------------------------
# Table structure for litorder
#----------------------------
CREATE TABLE `litorder` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
`address` varchar(50) NOT NULL default '',
`xdate` date NOT NULL default '0000-00-00',
`ref` varchar(20) NOT NULL default '',
`code1` varchar(50) NOT NULL default '',
`code2` varchar(50) NOT NULL default '',
`code3` varchar(50) NOT NULL default '',
`code4` varchar(50) NOT NULL default '',
`code5` varchar(50) NOT NULL default '',
`code6` varchar(50) NOT NULL default '',
`code7` varchar(50) NOT NULL default '',
`code8` varchar(50) NOT NULL default '',
`code9` varchar(50) NOT NULL default '',
`code10` varchar(50) NOT NULL default '',
`code11` varchar(50) character set latin1 collate latin1_bin NOT NULL default '',
`code12` varchar(50) NOT NULL default '',
`code13` varchar(50) NOT NULL default '',
`code14` varchar(50) NOT NULL default '',
`code15` varchar(50) NOT NULL default '',
`product1` varchar(100) NOT NULL default '0',
`product2` varchar(100) NOT NULL default '0',
`product3` varchar(100) NOT NULL default '0',
`product4` varchar(100) NOT NULL default '0',
`product5` varchar(100) NOT NULL default '0',
`product6` varchar(100) NOT NULL default '0',
`product7` varchar(100) NOT NULL default '0',
`product8` varchar(100) NOT NULL default '0',
`product9` varchar(100) NOT NULL default '0',
`product10` varchar(100) NOT NULL default '0',
`product11` varchar(100) NOT NULL default '0',
`product12` varchar(100) NOT NULL default '0',
`product13` varchar(100) NOT NULL default '0',
`product14` varchar(100) NOT NULL default '0',
`product15` varchar(100) NOT NULL default '0',
`price1` int(10) NOT NULL default '0',
`price2` int(10) NOT NULL default '0',
`price3` int(10) NOT NULL default '0',
`price4` int(10) NOT NULL default '0',
`price5` int(10) NOT NULL default '0',
`price6` int(10) NOT NULL default '0',
`price7` int(10) NOT NULL default '0',
`price8` int(10) NOT NULL default '0',
`price9` int(10) NOT NULL default '0',
`price10` int(10) NOT NULL default '0',
`price11` int(10) NOT NULL default '0',
`price12` int(10) NOT NULL default '0',
`price13` int(10) NOT NULL default '0',
`price14` int(10) NOT NULL default '0',
`price15` int(10) NOT NULL default '0',
`quantity1` int(10) NOT NULL default '0',
`quantity2` int(10) NOT NULL default '0',
`quantity3` int(10) NOT NULL default '0',
`quantity4` int(10) NOT NULL default '0',
`quantity5` int(10) NOT NULL default '0',
`quantity6` int(10) NOT NULL default '0',
`quantity7` int(10) NOT NULL default '0',
`quantity8` int(10) NOT NULL default '0',
`quantity9` int(10) NOT NULL default '0',
`quantity10` int(10) NOT NULL default '0',
`quantity11` int(10) NOT NULL default '0',
`quantity12` int(10) NOT NULL default '0',
`quantity13` int(10) NOT NULL default '0',
`quantity14` int(10) NOT NULL default '0',
`quantity15` int(10) NOT NULL default '0',
`amount1` int(10) NOT NULL default '0',
`amount2` int(10) NOT NULL default '0',
`amount3` int(10) NOT NULL default '0',
`amount4` int(10) NOT NULL default '0',
`amount5` int(10) NOT NULL default '0',
`amount6` int(10) NOT NULL default '0',
`amount7` int(10) NOT NULL default '0',
`amount8` int(10) NOT NULL default '0',
`amount9` int(10) NOT NULL default '0',
`amount10` int(10) NOT NULL default '0',
`amount11` int(10) NOT NULL default '0',
`amount12` int(10) NOT NULL default '0',
`amount13` int(10) NOT NULL default '0',
`amount14` int(10) NOT NULL default '0',
`amount15` int(10) NOT NULL default '0',
`totalNaira` double(20,0) NOT NULL default '0',
`totalDollar` int(20) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 4096 kB; InnoDB free: 4096 kB; InnoDB free: 409';
#----------------------------
# Records for table litorder
#----------------------------
insert into litorder values
(27, 'Sanyaolu Fisayo', 'SNIP', '2010-05-31', '', 'DL 001', 'DL 002', 'DL 003', '', '', '', '', '', '', '', '', '', '', '', '', 'AILMENT & PREVENTION DVD- ENGLISH', 'AILMENT & PREVENTION DVD- HAUSA', 'BEAUTY CD', '', '', '', '', '', '', '', '', '', '', '', '', 800, 800, 3000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 16, 16, 20, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 12800, 12800, 60000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '85600', 563),
(28, 'Irenonse Esther', 'Lagos,Nigeria', '2010-06-01', '', 'DL 005', 'DL 008', 'FC 004', '', '', '', '', '', '', '', '', '', '', '', '', 'GET HEALTHY DVD', 'YOUR FUTURE DVD', 'FOREVER FACE CAP (YELLOW)', '', '', '', '', '', '', '', '', '', '', '', '', 1000, 900, 2000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 2, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2000, 1800, 6000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '9800', 64),
(29, 'Kalu Lekway', 'Lagos, Nigeria', '2010-06-01', '', 'DL 001', 'DL 003', '', '', '', '', '', '', '', '', '', '', '', '', '', 'AILMENT & PREVENTION DVD- ENGLISH', 'BEAUTY CD', '', '', '', '', '', '', '', '', '', '', '', '', '', 800, 3000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2400, 18000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '20400', 133),
(30, 'Dele', 'Ilupeju', '2010-06-02', '', 'DL 001', 'DL 003', '', '', '', '', '', '', '', '', '', '', '', '', '', 'AILMENT & PREVENTION DVD- ENGLISH', 'BEAUTY CD', '', '', '', '', '', '', '', '', '', '', '', '', '', 800, 3000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 10, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 8000, 30000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '38000', 250);