Results 1 to 3 of 3

Thread: Faulty logic in 'Group by Clause' lesson in SQL2 course?

  1. #1
    Join Date
    May 2009
    Posts
    2

    Question Faulty logic in 'Group by Clause' lesson in SQL2 course?

    When building the query for exercise 3:

    "How many orders did each customer make? Use the items_ordered table. Select the customerid, number of orders they made, and the sum of their orders."

    My answers look significantly different from the results obtained by using the answer provided. Look at the third column, and compare the two results. When I read the source table it appears that some of the orders have multiple items associated with them. This should affect the sum that is requested. If all one does is sum the column, none of the multiple items are included in the total. The "answers" query structure is as follows:

    SELECT customerid, count(customerid), sum(price)
    FROM items_ordered
    GROUP BY customerid;

    This yields a result of:
    10101 6 320.75
    10298 5 118.88
    10299 2 1288
    10315 1 8
    10330 3 72.75
    10339 1 4.5
    10410 2 281.72
    10413 1 32
    10438 3 95.24
    10439 2 113.5
    10449 6 930.79

    I feel that you should be including the multiples by the following query:

    SELECT customerid, count(customerid), sum(quantity*price)
    FROM items_ordered
    GROUP BY customerid;

    which then results in:
    10101 6 813.95
    10298 5 147.88
    10299 2 1288
    10315 1 8
    10330 3 156.75
    10339 1 4.5
    10410 2 281.72
    10413 1 128
    10438 3 95.24
    10439 2 139
    10449 6 970.79

    Is my understanding of the question, or the presented data faulty? Or should we use the multiplier to get correct results?
    I'm only asking this because I'm a real neophyte at this SQL query thing - that's why I'm taking the tutorial lessons - and I'd like to be sure I'm reading this correctly. I did check my results and they are consistent with my logic.

  2. #2
    Join Date
    Dec 2009
    Posts
    2
    Pardon me if I am supposed to start a new thread regarding this; but I am a very newbie to both forums and SQL.

    I have a similar question regarding the third exercise in "Having Clause" lesson; I am not sure I understood the exercise; I typed in the following:

    select customerid, count(quantity), sum(price)
    from items_ordered
    group by customerid
    having count(quantity) > 1;

    I got the following result:

    10101 6 320.75
    10298 5 118.88
    10299 2 1288.00
    10330 3 72.75
    10410 2 281.72
    10438 3 95.24
    10439 2 113.50
    10449 6 930.79

    This result matches exactly the result I get from executing the statements given in the "Answers to these exercises" page!!

    (I include here the statements given in that page here for your convenience:

    SELECT customerid, count(customerid), sum(price)
    FROM items_ordered
    GROUP BY customerid
    HAVING count(customerid) > 1;
    )

    I think now I should have also gotten the record # 10413 with 4 orders with price 32.00 in my version of the answer.

    I would appreciate clarification on this that anyone can provide.

    Thanks in advance.

  3. #3
    Join Date
    Mar 2010
    Posts
    2

    SQL Course 2 Lesson 3 Ex 3 & Lesson 4 Ex 3

    I am calling "Group By Clause" Lesson, Lesson 3 and "Having Clause" Lesson, Lesson 4. "Ex" is for exercise or question

    First, Response to fisch4billf:
    Your logic is not the issue, I just think you gave the problem more thought than the developers had planned for. One would naturally assume that the price column is "per unit cost"; and based on the quantities and pricing, I think this is a fair assumption. So, yes, using the multiplier could be a more accurate answer. In the end, whether you use the multiplier or not you have more than adequately completed this exercise.

    Second, Response to reva:
    Yes, in this case you should have started another thread, if for no other reason than because your question is actually regarding Lesson 4 "Having Clause" exercise 3 (where as fisch4billf's was Lesson 3 Ex 3), but let us not dwell on that small detail.
    I am not sure how best to explain this, but here it goes. Let us just examine your code first. Ask yourself why, the "count(quantity)" displayed for customerid 10101 is only 6 and not 12. Realize that "count(quantity)" is actually counting the instances of the quantity column for customerid 10101 and not summing the actual values in the quantity column. The sum would be written by "sum(quantity)", not "count(quantity)".
    If you can see this (what I have just explained), then you will see that customerid 10413 cannot be displayed because the occurrence is only 1, even thought the actual value in the quantity column is 4. We are not evaluating "4", though. We are not evaluating "HAVING quantity > 1;" and we are not evaluating "HAVING sum(quantity) > 1;" instead we are just evaluating "HAVING count(quantity) > 1;"

    So, the main point here is that we are evaluating instances not sums or values. That is why coincidentally your code yields exactly the same as the answer code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •