Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query to Find The First Order For All Customers

Posted on 2010-09-15
17
Medium Priority
?
971 Views
Last Modified: 2012-05-10
I have a table like this:

-order_id
-order_date
-user_id

For each user, I want to find their *first* order only.  order_id is an auto increment and order_date is datetime.  Lowest ID or earliest date would be their first order.
0
Comment
Question by:trippy1976
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 3
  • +1
17 Comments
 
LVL 7

Expert Comment

by:tlovie
ID: 33684601
SELECT user_id, min(order_date) as min_order_date
FROM table
GROUP BY user_id
0
 
LVL 7

Expert Comment

by:tlovie
ID: 33684614
or you can do this:

SELECT user_id, min(order_id) as min_order_id
FROM table
GROUP BY user_id
0
 
LVL 4

Author Comment

by:trippy1976
ID: 33684789
Excellent, thank you.  Before I close this can I impose for one more quickie?

I'd like to know how many customers made their first order in each month/year

Select count(min(order_id)) as total_orders, MONTH(order_date) as order_month, YEAR(order_date) as order_year
from table
group by user_id
order by order_year, order_month asc

??  Am I close?  

Thanks!
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33684813
this article can help to write the query you want:http://www.experts-exchange.com/A_3203.html
0
 
LVL 7

Expert Comment

by:tlovie
ID: 33684872
count, sum, avg, min, max are called aggregate functions.  you cannot have them in your group by clause.  MONTH, YEAR, etc, are scalar functions, so if they are in your select statement, they must also be in your group by clause.

I'm not sure if you still want user_id in there, but it could be in our out.... if you want to take it out, you would remove it from the SELECT and the GROUP BY clauses.

select user_id, count(order_id), MONTH(order_date) as order_month, YEAR(order_date) as order_year
from table
group by user_id, MONTH(order_date), YEAR(order_date)
order by order_year, order_month asc
0
 
LVL 4

Author Comment

by:trippy1976
ID: 33686019
Thanks for the great replies tlovie, but maybe I confused by trying it myself.  

What I want is only a count of the orders made in a month that are that customer's *first* order.

The first query shows each first order with the date, but I get something like :

1   9/1/2010
2  9/2/2010
3  9/3/2010

etc.

Where col 1 is the order_id and col 2 is the date of the order.  This is great.

Now I'd like something like:

3  9   2010

Where col1 is the total first time orders in month 9 (col2) of 2010 (col3)

Is this doable in a single query?
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33687058
Like tloive said if you want to NOT GROUP BY user_id just take it out of both the select and group by areas.

Also I think year should group out first - altered SQL below:
select count(order_id), MONTH(order_date) as order_month, YEAR(order_date) as order_year
from table
group YEAR(order_date), MONTH(order_date)
order by order_year, order_month asc

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33687074
however this give you ALL orders.

try something that will give you a count of the MINIMUMs

try this and see if we get any errors:
select user_id, YEAR(order_date) as order_year, MONTH(order_date) as order_month, SUM(IF(MIN(order_id) <> NULL, 1, 0)) as first_orders
from table
group by user_id, YEAR(order_date), MONTH(order_date), order_date
order by order_year, order_month asc

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33687078
oops i mean try:
select user_id, YEAR(order_date) as order_year, MONTH(order_date) as order_month, SUM(IF(MIN(order_id) <> NULL, 1, 0)) as first_orders
from table
group by user_id, YEAR(order_date), MONTH(order_date)
order by order_year, order_month asc

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33687108
then you can SUPER-query the results:

here is an example of trippy1976's query w/ SUPER-QUERY
SELECT SUM(first_orders), order_year, order_month FROM (
Select count(min(order_id)) as first_orders, MONTH(order_date) as order_month, YEAR(order_date) as order_year
from table
group by user_id, order_year, order_month
order by order_date asc
) x
group by order_year, order_month

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33687116
fixed
SELECT SUM(first_orders), order_year, order_month FROM (
Select count(min(order_id)) as first_orders, MONTH(order_date) as order_month, YEAR(order_date) as order_year
from table
group by user_id, order_year, order_month
order by order_year, order_month asc
) x
group by order_year, order_month

Open in new window

0
 
LVL 7

Accepted Solution

by:
tlovie earned 2000 total points
ID: 33687122
Okay, I think I understand what you want..

Step 1:

select user_id, min(order_date) as min_order_date from table group by user_id
-- this gives us the date that each customer orders a product

Step 2:

select count(user_id), MONTH(min_order_date), YEAR(min_order_date) from (
select user_id, min(order_date) as min_order_date from table group by user_id ) as q
group by MONTH(min_order_date), YEAR(min_order_date)
-- use the step 1 subquery to find all the new customers by month

Give that a go... all you need to execute is the step 2 query.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33687129
nm order_date should sort
fixed.:
SELECT SUM(first_orders), order_year, order_month FROM (
Select count(min(order_id)) as first_orders, MONTH(order_date) as order_month, YEAR(order_date) as order_year
from table
group by user_id, order_year, order_month
order by order_date asc
) x
group by order_year, order_month

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33687181
fixed min parameter. try:
SELECT 
 SUM(first_orders) AS total_first_orders, 
 order_year, 
 order_month 
FROM 
(
 SELECT 
  COUNT(MIN(order_date)) AS first_orders, 
  MONTH(order_date) AS order_month, 
  YEAR(order_date) AS order_year
 FROM table
 GROUP BY user_id, YEAR(order_date), MONTH(order_date) 
 ORDER BY order_date ASC
) x
GROUP BY order_year ASC, order_month ASC

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33687212
the inner query selects first orders from EACH month from EACH customer.

say there is 3 customers:

first_order count|month|year

1|9|2010
1|10|2010
1|11|2010

1|9|2010
0|10|2010
1|11|2010

1|9|2010
0|10|2010
1|11|2010

Then the UPPER or SUPER query will take totals of matching year, month totals you end up with:

total first orders|year|month
3|2010|9
1|2010|10
3|2010|11
0
 
LVL 4

Author Closing Comment

by:trippy1976
ID: 33691674
Perfect on both counts, thanks!
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33696907
Thanks for the feedback. Which query did you end up using in the end?
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question