0

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 |
+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------+----------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+---------------+
Improve this question
2
  • I'm not sure if I understand you completely. You want result rows with the value NULL in columns bidresponseitemid and bidresponsepersonid not to show up? Or they do not show up and you want them to? Maybe include a example result set (you can get nice text ones by using the mysql command in a console/terminal) and describe what you want to have differently. "WHERE bidresponsepersonid IS NOT NULL" might be something your looking for. Commented Aug 15, 2010 at 7:30
  • thanks thomas. i will clarify above. Commented Aug 15, 2010 at 7:31

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

4 Comments

you're right, i could do the processing client-side, but i just have a feeling that there must be a much easier way... if only i could apply some sort of WHERE rule to the GROUP clause...
GROUP BY will merge your rows in the way you tell it to. With a GROUP BY items.id you will always just get a row for that id. I'm not aware of a function that does what you want it to do. If you want to do it all with SQL you can always send 2 queries e.g.: SELECT SUM(tagsitems.quantity) AS totalquantity, tagsitems.itemid FROM tagsitems GROUP BY tagitems.itemid and your other one. Btw: don't use *, except you really are using 100%.
it turned out that simply changing the 3rd field in the group by clause to itemscontacts.personid instead of bidresponses.personid gives me exactly what i need!
Would be nice if you post the final query and a example result to it as an answer. I'm not 100% sure what you needed in the end.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.