[skatingrocker17]

I'm stuck on a problem on my MySql homework. The question is:

Quote

How many books has each customer ordered? List the customer number and the total number of books (a calculated field) ordered by the total number of books in descending order.

My thought is to update the quantity each time the database sees that (for example) customer number 1004 makes an order. BUT, how and where do I store this new variable without adding it to the database? I just want to display it.

Thanks

[rgfirefly24]

Sql gives you different Aggregate functions to help you solve these types of problems. What you need in your case is the SUM to get a total of the number of books a customer has ordered. You will also need to use a GROUP BY clause and an ORDER BY clause in order to complete this assignment.

EX:

SELECT <Column1>,SUM(<Column2>)
FROM <Table>
WHERE <Conditions>
GROUP BY <Column1>
ORDER BY <Conditions>

This post has been edited by rgfirefly24: 16 April 2012 - 05:24 AM

[skatingrocker17]

rgfirefly24, on 16 April 2012 - 05:22 AM, said:

Sql gives you different Aggregate functions to help you solve these types of problems. What you need in your case is the SUM to get a total of the number of books a customer has ordered. You will also need to use a GROUP BY clause and an ORDER BY clause in order to complete this assignment.

EX:

SELECT <Column1>,SUM(<Column2>)
FROM <Table>
WHERE <Conditions>
GROUP BY <Column1>
ORDER BY <Conditions>

Wouldn't that add columns 1 and 2 together? For every customerID number that is the name I want to add the quantities together so it will only display the customerID number ones and the total quantity of everything they've ordered as opposed to multiple orders from the same customerID number.

I keep getting errors for some reason with that. I didn't use where because I don't have any conditions unless they would be where customerID = customerID.

[Atli]

skatingrocker17, on 16 April 2012 - 02:59 PM, said:

Wouldn't that add columns 1 and 2 together?

No. The SUM function adds all the values from the selected column in the specified grouping.

For example, say you have this (rather pointless) table:

+----------+-----------+------------+
| forum_id | thread_id | post_count |
+----------+-----------+------------+
|        1 |         1 |          5 |
|        1 |         2 |          2 |
|        2 |         3 |          1 |
|        2 |         4 |          8 |
|        2 |         5 |          3 |
+----------+-----------+------------+

If you were to execute this query on it:

SELECT 
	forum_id,
	COUNT(thread_id) AS thread_count,
	SUM(post_count) AS post_count
FROM pointlessness
GROUP BY forum_id;

This is what you would get:

+----------+--------------+------------+
| forum_id | thread_count | post_count |
+----------+--------------+------------+
|        1 |            2 |          7 |
|        2 |            3 |         12 |
+----------+--------------+------------+

It sums up all the post count values and thread IDs for each forum ID and displays them, one forum ID per row.

skatingrocker17, on 16 April 2012 - 02:59 PM, said:

I keep getting errors for some reason with that.

Show us the query you are using, and the error you are getting.

[skatingrocker17]

Here's an example of some of the data in the order database:
+---------+-----+-------+------------+--------+
| orderid | bid | quant | day | custid |
+---------+-----+-------+------------+--------+
| 1 | 24 | 1 | 2003-11-02 | 1004 |
| 2 | 25 | 1 | 2001-12-01 | 1005 |
| 3 | 13 | 2 | 2002-02-20 | 1006 |
| 4 | 14 | 5 | 2001-11-01 | 1007 |
| 5 | 16 | 1 | 2002-03-02 | 1008 |
| 6 | 65 | 1 | 2002-02-12 | 1008 |
| 7 | 102 | 1 | 2002-12-22 | 1001 |
| 8 | 78 | 2 | 2003-03-03 | 1002 |


I just tried to simplify it to see if I can get it working before I start adding things:
mysql> select <custid>, SUM(<quant>)
-> from <orders>;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<custid>, SUM(<quant>)
from <orders>' at line 1

[Atli]

Remove the < and > from around the identifiers. They shouldn't be there.

[skatingrocker17]

Atli, on 16 April 2012 - 09:56 AM, said:

Remove the < and > from around the identifiers. They shouldn't be there.

I just thought that was something that more advanced people do..

Okay, so I got it partially working but when I try to order by quantity descending I get more errors. The errors really aren't helpful.


mysql> select custid, sum(quant) from orders group by custid;
+--------+------------+
| custid | sum(quant) |
+--------+------------+
| 1001 | 3 |
| 1002 | 6 |
| 1003 | 10 |
| 1004 | 6 |
| 1005 | 4 |
| 1006 | 4 |
| 1007 | 9 |
| 1008 | 4 |
| 1009 | 15 |
+--------+------------+
9 rows in set (0.00 sec)

mysql>
mysql>
mysql> select custid, sum(quant) group by custid order by quant desc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by custid order by quant desc' at line 1
mysql>
mysql> select custid, sum(quant) group by custid AND order by quant desc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by custid AND order by quant desc' at line 1
mysql>

[Atli]

When there is a syntax error like that, the error message MySQL shows you will tell you where the error occurred. In this case the problem is right before the "group by" clause. The key is to examine what comes immediately before that and see if you can't spot anything out of order.

If you compare those two queries with the one above it, the one that worked, then you will see that you are forgetting the FROM clause.

skatingrocker17, on 16 April 2012 - 05:10 PM, said:

Atli, on 16 April 2012 - 09:56 AM, said:

Remove the < and > from around the identifiers. They shouldn't be there.

I just thought that was something that more advanced people do..

Nope, but we do sometimes use back-ticks ` (not to be confused with single-quotes ') around identifiers. It allows you to use special characters that won't work normally.

SELECT `my uber data field`
FROM `my special table`
WHERE `nothing at all` IS NOT NULL

This should work, but if you remove the ` chars from around the field and table identifiers, you'll get no end of syntax errors.

This is especially useful when you want to use reserved keywords, like "time" or "table" as identifiers. (Although, you really should avoid that. It's just asking for trouble.)

This post has been edited by Atli: 16 April 2012 - 10:34 AM

[skatingrocker17]

EDIT: Figured it out.

Magic code:

mysql> select custid, sum(quant) from orders group by quant desc, custid;

This post has been edited by skatingrocker17: 16 April 2012 - 05:32 PM

[rgfirefly24]

I wouldn't use quant desc in your group by, I would move that to an order by and do ORDER BY SUM(quant) DESC. You want to only put in your group by the least amount you need to.