# Related Sites > SQL Course >  Faulty logic in 'Group by Clause' lesson in SQL2 course?

## fisch4billf

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.

----------


## reva

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.

----------


## SwiftSQL

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.

----------

