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);

Recommended Answers

All 3 Replies

it is well organized. I have to take in 15 values at a time.

Member Avatar for rajarajan2017
select (quantity1+quantity2+quantity3+quantity4+quantity5+quantity6+quantity7+quantity8+quantity9+quantity10+quantity11+quantity12+quantity13+quantity14+quantity15) as TotalSold from litorder where id=28;

return 7

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.