1

Hi I'm trying to count order records with users who made more than one order in a month like 2018-01-01 to 2018-02-01.

Order table
id   user_id   date
1    12        2017-01-02
2    23        2018-01-03
3    12        2018-01-04
4    12        2018-01-08
....

User table
id
12
23
....

What I need is a number of Order records in a certain month

Output

output should be 2 which means user_id 12 is already made an order in 2017-01-02 so id in Order table 3 and 4 are records I need to count. Then summate the total records

I think I need to get order records first and find order records again with certain user_id. But I'm stuck Is there a way to accomplish this task? Thanks

Community
  • 1
  • 1
Eric Lee
  • 700
  • 2
  • 9
  • 30
  • "What I need is a number of Order records in a certain month"...per user, or in total? You mention users in the rest of the question, but then omit them from the statement of requirement? Also, what have you tried so far? COUNT(), GROUP BY and MONTH() or MONTHNAME() are all your friends. – ADyson Jul 25 '18 at 10:28
  • @ADyson in total – Eric Lee Jul 25 '18 at 10:28
  • So where does "find order records again with certain user_i" come into it, then? Not sure I follow you. Maybe best to give us an example of your expected output, then we are 100% clear. Also see my edited comment above. You should be able to research this yourself and at least try some things. I would be surprised if there are not examples of something very very similar online already, maybe even in past questions on this site. – ADyson Jul 25 '18 at 10:29
  • @ADyson I just updated the output I want – Eric Lee Jul 25 '18 at 10:34
  • so actually the requirement is "find me the total number of orders made by user 12 in January 2018?" ? Is that correct? The wording in your question, and in your comments, is a bit contradictory, to be honest. Please try to be 100% clear what the query criteria actually are. I am pretty certain you only need one single query to accomplish this, though. Like I said, learn about GROUP BY queries. – ADyson Jul 25 '18 at 10:38
  • @ADyson That is correct. I wan to find the total number of orders made by user 12 in January 2018 and the user 12 should be many. Im sorry i thought it could be done with single query. So i didnt make clear what i want. – Eric Lee Jul 25 '18 at 11:01
  • "the user 12 should be many"...not sure what this means? Yes, it can be done in one query. – ADyson Jul 25 '18 at 11:05
  • @ADyson what i means is I want number of total order records accumulated with number of order records which made by user who made more than one order. Did i make clear for you? – Eric Lee Jul 25 '18 at 11:12
  • so you want two different output values - the total orders for the month, and the total orders for the specific user? The question simply says the output should be "2", which is the total for the user. In your example data, the total number of orders for the month, for all users, is "3". So are you saying you want to output both "3" and "2"? – ADyson Jul 25 '18 at 11:39

1 Answers1

3

use aggregate function count and group by 

  select u.id,count(*) from Order as O inner join user as u
    on O.user_id= u.id

    where date>='2018-01-01' and date <'2018-02-01'
    group by u.id
    having count(*)>1

http://sqlfiddle.com/#!9/57c69b/1

Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • What i want to find is the total records summed with the order records in each column from the sqlfiddle.com – Eric Lee Jul 25 '18 at 11:30