CODEWITHSUNDEEP
sqlmysql
Jordan Sitkin
Jordan Sitkin

Reputation: 2343

SQL GROUP BY and NULL values: How can I group my results by only non-null values?

My query:

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

Without the SUM() and GROUP BY clauses this query returns the desired results, minus the important totalquantity value.

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.

EDIT: I want the entries with NULL values in the bidresponses table to show up. With the current GROUP BY clause they are not. I should note that the only reason I am using the grouping at all is so that I can get the totalquantity value. Here is an example of the query output without the SUM() and GROUP BY clauses:

+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------------------+---------------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+
| 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                 | [email protected]      |               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            | [email protected]         |               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     | [email protected] | [email protected] |               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     | [email protected]  | [email protected]  |               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                 | [email protected]      |               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                 | [email protected]      |               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     | [email protected] | [email protected] |               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     | [email protected] | [email protected] |               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     | [email protected]  | [email protected]  |               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     | [email protected]  | [email protected]  |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-10 19:18:27 | NULL                |              NULL |                NULL |
+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------------------+---------------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+

Here is an example of the query output with the SUM() and GROUP BY clauses:

+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------+----------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+---------------+
| 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     | [email protected] |               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     | [email protected] |               NULL |   NULL | NULL        |     NULL | NULL | NULL             |        NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 |              NULL |                NULL |          NULL |
+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------+----------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+---------------+

Upvotes: 0

Views: 4978

Answers (2)

bbadour
bbadour

Reputation: 616

You need to include all of the significant un-aggregated columns in your GROUP BY clause. Right now, you do not group by createdAt etc.

Upvotes: 1

Thomas
Thomas

Reputation: 3044

If you need all those rows SUM() and the therefore required GROUP BY will not be a good solution.

The best solution probably will be to just add the quantities up when processing/looping through all rows in your client code/script.

Alternatively you could query twice, once with GROUP BY and SUM() to get the quantity for each items.id however even doing a extra loop through all the rows in your client code will probably be faster than querying twice, might also depend on the size of the result.

Upvotes: 0

Related Questions