title: MySQL Sum on matching set of data
body: <p>This is my first post in this forum. I normally find ans to my problems when search but this is the first time I needed to post this question. Any help would be highly appreciated.</p>
  
  <p>I have a table called "gjournal_main" </p>
  
  <pre><code>`dateinput` datetime NOT NULL COMMENT 'Input Date',
  `datepaid` date NOT NULL COMMENT 'Date of Invoice',
  `invoiceno` varchar(12) NOT NULL COMMENT 'Invoice #',
  `bookno` varchar(12) NOT NULL,
  PRIMARY KEY (`invoiceno`),
  KEY `invoiceno` (`invoiceno`)
  </code></pre>
  
  <p>and another with transaction detials "general_journal" (or items)</p>
  
  <pre><code>`eid` int(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entry ID',
  `tid` varchar(12) NOT NULL COMMENT 'Transaction ID or Inv no',
  `ref` varchar(15) NOT NULL COMMENT 'Reference no',
  `notes` varchar(30) NOT NULL COMMENT 'Narations',
  `accountname` varchar(50) NOT NULL COMMENT 'AC Title',
  `debit` int(12) NOT NULL,
  `credit` int(12) NOT NULL,
  PRIMARY KEY (`eid`),
  KEY `tid` (`tid`),
  KEY `ref` (`ref`)
  </code></pre>
  
  <p>I want to do a search on "bookno" and list all records with matching book number (eg 1254) several invoiceno can have one bookno.
  I want results something like this ....</p>
  
  <pre><code>------------------------------------------------------
  inputdate   |invoiceno |bookno|accounttitle|debit|credit
  ------------------------------------------------------
  2011-05-29  | 1        |1254  |item a      |12   |0
  2011-05-29  | 1        |1254  |item b      |0    |3
  2011-05-29  | 1        |1254  |item b      |1    |3
                               **TOTAL       |13   |6**
  2010-01-06  | 2        |900   |item a      |10   |0
  2010-01-06  | 2        |900   |item b      |50   |0
  2010-01-06  | 2        |900   |item c      |10   |30
  2010-01-06  | 2        |900   |item d      |10   |0
                               **TOTAL       |80   |30**
  
  ------------------------------------------------------
  </code></pre>
  
  <p>I want a break with sum of debit and credit  after listing invoice number 1 and so on how can I do that ?</p>
  
  <p>thanks a lot</p>