title: SQL GROUP BY and NULL values: How can I group my results by only non-null values?
body: <p>My query:</p>
  
  <pre><code>SELECT *,
      contacts.createdAt AS contactcreatedAt,
      contacts.updatedAt AS contactupdatedAt,
      bidresponses.itemid AS bidresponseitemid,
      bidresponses.personid AS bidresponsepersonid,
      SUM(tagsitems.quantity) AS totalquantity
  FROM items
  LEFT OUTER JOIN tagsitems ON items.id = tagsitems.itemid
  LEFT OUTER JOIN itemscontacts ON items.id = itemscontacts.itemid
  LEFT OUTER JOIN contacts ON itemscontacts.contactid = contacts.id
  LEFT OUTER JOIN bidresponses ON items.id = bidresponses.itemid AND itemscontacts.personid = bidresponses.personid
  LEFT OUTER JOIN bidtemplatefields ON bidresponses.bidtemplatefieldid = bidtemplatefields.id
  WHERE ( (items.id = 70687 OR items.id = 70595) AND itemscontacts.relationship = 's' ) AND ( items.deletedAt IS NULL )
  GROUP BY items.id, tagsitems.itemid, bidresponses.personid, bidresponses.bidtemplatefieldid
  ORDER BY items.id ASC
  </code></pre>
  
  <p>Without the <strong>SUM()</strong> and <strong>GROUP BY</strong> clauses this query returns the desired results, minus the important totalquantity value.</p>
  
  <p>The task that has got me stumped at the moment is constructing the GROUP BY clause so that it "ignores" NULL or missing values from bidresponses. The data is such that the results will be a mixed set of items - some have entries in the bidresponses table and some do not.</p>
  
  <p><strong>EDIT</strong>:
  I want the entries with NULL values in the bidresponses table to show up. With the current GROUP BY clause they are not. <strong>I should note that the only reason I am using the grouping at all is so that I can get the totalquantity value</strong>. Here is an example of the query output <strong>without</strong> the SUM() and GROUP BY clauses:</p>
  
  <pre><code>+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------------------+---------------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+
  | id    | itemtypeid | code | description               | cost | unittypeid | projectid | companyid | createdAt           | updatedAt           | deletedAt | unittype | tagid | itemid | quantity | itemid | contactid | personid | sentdate            | responsedate | bidtemplateid | relationship | awarddate | assigndate | id   | companyid | personid | companyidOwner | parentContactid | createdAt           | updatedAt           | firstName | lastName | company             | email               | bidtemplatefieldid | itemid | bidresponse | personid | id   | bidtemplatefield | fieldtypeid | contactcreatedAt    | contactupdatedAt    | bidresponseitemid | bidresponsepersonid |
  +-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------------------+---------------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+
  | 70595 |          1 | NULL | HD Banners                | NULL |       NULL |         7 |         1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL      | each     |  NULL |   NULL |     NULL |  70595 |        16 |    34789 | 2010-08-14 22:37:01 |         NULL |             1 | s            |      NULL |       NULL |   16 |      NULL |     NULL |              1 |            NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL      | NULL     | sdf                 | 23523@wokd.com      |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 |              NULL |                NULL |
  | 70595 |          1 | NULL | HD Banners                | NULL |       NULL |         7 |         1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL      | each     |  NULL |   NULL |     NULL |  70595 |        22 |    34794 | 2010-08-14 18:44:02 |         NULL |             1 | s            |      NULL |       NULL |   22 |      NULL |    34794 |              1 |            NULL | 2010-08-09 19:56:28 | 2010-08-10 13:55:03 | NULL      | NULL     | anewwwww            | hmm@hmm.com         |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-09 19:56:28 | 2010-08-10 13:55:03 |              NULL |                NULL |
  | 70595 |          1 | NULL | HD Banners                | NULL |       NULL |         7 |         1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL      | each     |  NULL |   NULL |     NULL |  70595 |        27 |    34797 | 2010-08-14 22:36:59 |         NULL |             1 | s            |      NULL |       NULL |   27 |      NULL |     NULL |              1 |            NULL | 2010-08-10 19:11:52 | NULL                | NULL      | NULL     | 3k3jdjhgj@wrwer.com | 3k3jdjhgj@wrwer.com |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-10 19:11:52 | NULL                |              NULL |                NULL |
  | 70595 |          1 | NULL | HD Banners                | NULL |       NULL |         7 |         1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL      | each     |  NULL |   NULL |     NULL |  70595 |        28 |    34798 | 2010-08-14 22:37:00 |         NULL |             1 | s            |      NULL |       NULL |   28 |      NULL |     NULL |              1 |            NULL | 2010-08-10 19:18:27 | NULL                | NULL      | NULL     | 3838474@234234.com  | 3838474@234234.com  |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-10 19:18:27 | NULL                |              NULL |                NULL |
  | 70687 |          1 | NULL | Editing and adding labels | NULL |       NULL |         7 |         1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL      | each     |    12 |  70687 |     NULL |  70687 |        16 |    34789 | 2010-08-14 22:37:01 |         NULL |             1 | s            |      NULL |       NULL |   16 |      NULL |     NULL |              1 |            NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL      | NULL     | sdf                 | 23523@wokd.com      |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 |              NULL |                NULL |
  | 70687 |          1 | NULL | Editing and adding labels | NULL |       NULL |         7 |         1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL      | each     |     2 |  70687 |     NULL |  70687 |        16 |    34789 | 2010-08-14 22:37:01 |         NULL |             1 | s            |      NULL |       NULL |   16 |      NULL |     NULL |              1 |            NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL      | NULL     | sdf                 | 23523@wokd.com      |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 |              NULL |                NULL |
  | 70687 |          1 | NULL | Editing and adding labels | NULL |       NULL |         7 |         1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL      | each     |    12 |  70687 |     NULL |  70687 |        27 |    34797 | 2010-08-14 22:36:59 |         NULL |             1 | s            |      NULL |       NULL |   27 |      NULL |     NULL |              1 |            NULL | 2010-08-10 19:11:52 | NULL                | NULL      | NULL     | 3k3jdjhgj@wrwer.com | 3k3jdjhgj@wrwer.com |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-10 19:11:52 | NULL                |              NULL |                NULL |
  | 70687 |          1 | NULL | Editing and adding labels | NULL |       NULL |         7 |         1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL      | each     |     2 |  70687 |     NULL |  70687 |        27 |    34797 | 2010-08-14 22:36:59 |         NULL |             1 | s            |      NULL |       NULL |   27 |      NULL |     NULL |              1 |            NULL | 2010-08-10 19:11:52 | NULL                | NULL      | NULL     | 3k3jdjhgj@wrwer.com | 3k3jdjhgj@wrwer.com |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-10 19:11:52 | NULL                |              NULL |                NULL |
  | 70687 |          1 | NULL | Editing and adding labels | NULL |       NULL |         7 |         1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL      | each     |    12 |  70687 |     NULL |  70687 |        28 |    34798 | 2010-08-14 22:37:00 |         NULL |             1 | s            |      NULL |       NULL |   28 |      NULL |     NULL |              1 |            NULL | 2010-08-10 19:18:27 | NULL                | NULL      | NULL     | 3838474@234234.com  | 3838474@234234.com  |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-10 19:18:27 | NULL                |              NULL |                NULL |
  | 70687 |          1 | NULL | Editing and adding labels | NULL |       NULL |         7 |         1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL      | each     |     2 |  70687 |     NULL |  70687 |        28 |    34798 | 2010-08-14 22:37:00 |         NULL |             1 | s            |      NULL |       NULL |   28 |      NULL |     NULL |              1 |            NULL | 2010-08-10 19:18:27 | NULL                | NULL      | NULL     | 3838474@234234.com  | 3838474@234234.com  |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-10 19:18:27 | NULL                |              NULL |                NULL |
  +-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------------------+---------------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+
  </code></pre>
  
  <p>Here is an example of the query output <strong>with</strong> the SUM() and GROUP BY clauses:</p>
  
  <pre><code>+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------+----------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+---------------+
  | id    | itemtypeid | code | description               | cost | unittypeid | projectid | companyid | createdAt           | updatedAt           | deletedAt | unittype | tagid | itemid | quantity | itemid | contactid | personid | sentdate            | responsedate | bidtemplateid | relationship | awarddate | assigndate | id   | companyid | personid | companyidOwner | parentContactid | createdAt           | updatedAt           | firstName | lastName | company | email          | bidtemplatefieldid | itemid | bidresponse | personid | id   | bidtemplatefield | fieldtypeid | contactcreatedAt    | contactupdatedAt    | bidresponseitemid | bidresponsepersonid | totalquantity |
  +-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------+----------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+---------------+
  | 70595 |          1 | NULL | HD Banners                | NULL |       NULL |         7 |         1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL      | each     |  NULL |   NULL |     NULL |  70595 |        16 |    34789 | 2010-08-14 22:37:01 |         NULL |             1 | s            |      NULL |       NULL |   16 |      NULL |     NULL |              1 |            NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL      | NULL     | sdf     | 23523@wokd.com |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 |              NULL |                NULL |          NULL |
  | 70687 |          1 | NULL | Editing and adding labels | NULL |       NULL |         7 |         1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL      | each     |    12 |  70687 |     NULL |  70687 |        16 |    34789 | 2010-08-14 22:37:01 |         NULL |             1 | s            |      NULL |       NULL |   16 |      NULL |     NULL |              1 |            NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL      | NULL     | sdf     | 23523@wokd.com |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 |              NULL |                NULL |          NULL |
  +-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------+----------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+---------------+
  </code></pre>