Home > SQL > SQL – SELECT part II

SQL – SELECT part II

I’m still working on SQL Course2, working on the more advanced parameters.

Running the practice query I made a mistake or maybe not? you’ll be the judge:

Based on item_oreder table that has the following columns – customerid, order_date, item, quantity, price I was asked to query the following:

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 if they purchased more than 1 item.

This is my code and the result:

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

I was reading “purchased more than 1 item” as quantity and as I understand it (and this is more of an English than SQL understanding) if the customer ordered 2 items or more in one purchase it counts to this matter.

The author of the course think differently as the suggested code is a bit different:

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

Unlike my code (and understanding), this solution count the number of different orders and not the quantity of items per order. The difference between the suggested solution and my solution is 4 Lawnchairs and maybe an English grammar class ;)

This is a great example to the power of the SELECT command options and their flexibility but it is also a reminder to the thin layer between nailing or failing.

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 44 other followers

%d bloggers like this: