title: Get the total credit and total debit in one row based on date in knex or mysql
body: <p>Consider the following schema:</p>
  <pre><code>DROP TABLE IF EXISTS balance_sheet;
  
  CREATE TABLE `balance_sheet` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `amount` decimal(8,2) NOT NULL,
    `type` enum('debit','credit') NOT NULL DEFAULT 'debit',
    `remaining_amount` decimal(8,2) NOT NULL,
    `shop_id` bigint(20) unsigned NOT NULL,
    `sales_man_id` bigint(20) unsigned NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `billing_id` bigint(20) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`)
    );
    
  INSERT INTO balance_sheet VALUES
  (124, 40,'debit' ,540,  1,1,'2020-11-27 22:22:08','2020-11-27 22:22:08',82),
  (125,290,'credit',250,  1,1,'2020-11-27 22:26:08','2020-11-27 22:26:08',null),
  (126,175,'debit' ,675,  3,1,'2020-11-28 05:37:17','2020-11-28 05:37:17',83),
  (127,200,'debit' ,500,218,1,'2020-11-28 05:37:40','2020-11-28 05:37:40',84),
  (128, 10,'debit' , 15,  2,2,'2020-11-28 08:44:44','2020-11-28 08:44:44',85),
  (129,185,'debit' ,385, 20,2,'2020-11-28 08:44:44','2020-11-28 08:44:44',85);
  
  DROP TABLE IF EXISTS salesman;
  
    CREATE TABLE `salesman` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `first_name` varchar(255) NOT NULL,
    `last_name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
  );
  
  INSERT INTO salesman VALUES
  (1,'Abdulmoiz','Ahmer'),
  (2,'Umair','Manzoor');
  </code></pre>
  <p>I want to get salesman daily record according to the date(How much he debited and how much he credited on a certain date and what is the final remaining balance of the date).</p>
  <p>Required Fields:
  <strong>created_at,credit,debit,first_name,last_name,sales_man_id</strong></p>
  <p>What i did so far:</p>
  <pre><code>knex('balance_sheet as bs')
                  .join('salesman as sm', 'bs.sales_man_id', 'sm.id')
                  .select('bs.sales_man_id', 'bs.type', 'bs.created_at', 'sm.first_name', 'sm.last_name', 'bs.created_at')
                  .sum('amount as amount')
                  .groupBy('bs.sales_man_id', 'bs.type')
                  .groupByRaw('DATE(bs.created_at)');
  </code></pre>
  <p>Result of what i did:</p>
  <pre><code>&quot;data&quot;: [
      {
          &quot;sales_man_id&quot;: 2,
          &quot;type&quot;: &quot;debit&quot;,
          &quot;created_at&quot;: &quot;2020-11-28T08:44:44.000Z&quot;,
          &quot;first_name&quot;: &quot;Umair&quot;,
          &quot;last_name&quot;: &quot;Manzoor&quot;,
          &quot;amount&quot;: 195
      },
      {
          &quot;sales_man_id&quot;: 1,
          &quot;type&quot;: &quot;credit&quot;,
          &quot;created_at&quot;: &quot;2020-11-27T22:26:08.000Z&quot;,
          &quot;first_name&quot;: &quot;Abdulmoiz&quot;,
          &quot;last_name&quot;: &quot;Ahmer&quot;,
          &quot;amount&quot;: 290
      },
      {
          &quot;sales_man_id&quot;: 1,
          &quot;type&quot;: &quot;debit&quot;,
          &quot;created_at&quot;: &quot;2020-11-27T22:22:08.000Z&quot;,
          &quot;first_name&quot;: &quot;Abdulmoiz&quot;,
          &quot;last_name&quot;: &quot;Ahmer&quot;,
          &quot;amount&quot;: 415
      }
  ]
  </code></pre>
  <p>As you can see my query is generating two rows for same record one for total debit of the day and one for total credit of the day but i want them to be in same row. Like below:</p>
  <pre><code> &quot;data&quot;: [
          {
              &quot;sales_man_id&quot;: 2,
              &quot;created_at&quot;: &quot;2020-11-28T08:44:44.000Z&quot;,
              &quot;first_name&quot;: &quot;Umair&quot;,
              &quot;last_name&quot;: &quot;Manzoor&quot;,
              &quot;debit&quot;: 195,
              &quot;credit&quot;: 0,
          },
          {
              &quot;sales_man_id&quot;: 1,
              &quot;created_at&quot;: &quot;2020-11-27T22:26:08.000Z&quot;,
              &quot;first_name&quot;: &quot;Abdulmoiz&quot;,
              &quot;last_name&quot;: &quot;Ahmer&quot;,
              &quot;credit&quot;: 290,
              &quot;debit&quot;: 415
          }
      ]
  </code></pre>
  <p>If you can not write a knex query that's ok give an sql one i will convert it myself.</p>