January 20, 2011 at 7:40 pm
Please help me, I can't find any proper tutorial that would properly help me on this.
I have the following database structure, I need a SQL query to retrieve a list of the 5 most popular states that had orders.
--------------------------------------------
Orders
------
id
date
shipping_amount
order_status
customer_id (Customers.id)
Customers
----------
id
first_name
last_name
city
state
The output should look something like this:
+-----------+------------+
| State | # Orders |
+-----------+------------+
| NY | 55 |
| CA | 40 |
| NJ | 33 |
| FL | 21 |
| MO | 12 |
+-----------+------------+
January 20, 2011 at 8:03 pm
What have you tried so far to get the desired result?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 20, 2011 at 8:30 pm
I really have no clue.
I figure there is a SUM() involved.
I fixed the wrong table structure in top of this message.
January 20, 2011 at 9:18 pm
There is more than one way to do this one. A common method would be to use count(), group by, and order by.
See what you can get by looking those up and then post again if you run into another roadblock.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 20, 2011 at 9:26 pm
I really need help. Anyone can help me compose this I really appreciate it. SQL is just not my thing.
I know the parts you mentioned but its just not there for me in putting the parts together after looking them up.
January 20, 2011 at 10:00 pm
If you took the time to do a little research in Books Online, you would probably have been able to figure this one out yourself. Please take the time to read about the various parts of the following query:
select
cust.state,
COUNT(ord.id) as NumOrders
from
dbo.Customers cust
inner join do.Orders ord
on (cust.id = ord.customer_id)
group by
cust.state;
January 21, 2011 at 6:59 am
Lynn Pettis (1/20/2011)
If you took the time to do a little research in Books Online, you would probably have been able to figure this one out yourself.
There are samples in Books Online as well as the Textbook for this class that should have helped to nail this one.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 21, 2011 at 7:43 am
CirquedeSQLeil (1/21/2011)
Lynn Pettis (1/20/2011)
If you took the time to do a little research in Books Online, you would probably have been able to figure this one out yourself.There are samples in Books Online as well as the Textbook for this class that should have helped to nail this one.
True, but after the way things went at work yesterday I had to do something for someone.
January 21, 2011 at 8:39 am
Lynn Pettis (1/21/2011)
CirquedeSQLeil (1/21/2011)
Lynn Pettis (1/20/2011)
If you took the time to do a little research in Books Online, you would probably have been able to figure this one out yourself.There are samples in Books Online as well as the Textbook for this class that should have helped to nail this one.
True, but after the way things went at work yesterday I had to do something for someone.
I see. This one was the second "book" question by this person yesterday. I answered the other and was hoping that he would try something on this one.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 21, 2011 at 9:44 am
CirquedeSQLeil (1/21/2011)
Lynn Pettis (1/21/2011)
CirquedeSQLeil (1/21/2011)
Lynn Pettis (1/20/2011)
If you took the time to do a little research in Books Online, you would probably have been able to figure this one out yourself.There are samples in Books Online as well as the Textbook for this class that should have helped to nail this one.
True, but after the way things went at work yesterday I had to do something for someone.
I see. This one was the second "book" question by this person yesterday. I answered the other and was hoping that he would try something on this one.
Not on the forums as much as I'd like right, hopefully that can change soon.
January 21, 2011 at 9:47 am
Lynn Pettis (1/21/2011)
CirquedeSQLeil (1/21/2011)
Lynn Pettis (1/21/2011)
CirquedeSQLeil (1/21/2011)
Lynn Pettis (1/20/2011)
If you took the time to do a little research in Books Online, you would probably have been able to figure this one out yourself.There are samples in Books Online as well as the Textbook for this class that should have helped to nail this one.
True, but after the way things went at work yesterday I had to do something for someone.
I see. This one was the second "book" question by this person yesterday. I answered the other and was hoping that he would try something on this one.
Not on the forums as much as I'd like right, hopefully that can change soon.
Hijacking this thread - :w00t::-D
I am not on much currently with all that is going on. It would be nice to be on more.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 21, 2011 at 10:22 am
Can't from work and the evenings are busy doing other stuff.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply