0

Im trying to select only ids of customers that have ordered atleast once every year in a specific time period for example 2010 - 2017

example:
1. customer ordered in 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017 should be shown
2. customer ordered in 2010, 2011, 2012,2013,2014,2015, 2017 should not be shown

my query counts in all years not within the period

o_id    o_c_id  o_type      o_date
1345    13    TA          2015-01-01
7499    13    TA          2015-01-16
7521    14    GA          2015-01-08
7566    14    TA          2016-01-24
7654    16    FB          2016-01-28

c_id    c_name  c_email
13      Anderson  example@gmail.com          
14      Pegasus   example@gmail.com
15      Miguel    example@gmail.com
16      Megan     example@gmail.com

my query:

select c.id, c.name, count(*) as counts, year(o.date)
from orders o
join customer c on o.c_id=c.id
where year(o.date) > 2009
group oy c.id
having count(*) > 7
Regan
  • 7
  • 5
  • There is no customer that satisfies these requirements. See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Nov 22 '17 at 13:11

1 Answers1

0

You need a table with all the years so you can check if user order that year. I create a sample with only two years because that is what in your sample data.

You can use this to create a list of years:

How to get list of dates between two dates in mysql select query

Also I use ranges for years so you can use index at the moment of the join.

If you already have a table users you can replace the subquery

SQL DEMO

SELECT user_id, COUNT(o_id) as total_years
FROM years y
CROSS JOIN (SELECT DISTINCT `o_c_id` as `user_id` FROM `orders`) as users
LEFT JOIN orders o
  ON o.`o_date` >= y.`year_begin`
 AND o.`o_date` < y.`year_end`
 AND o.`o_c_id` = `user_id`
GROUP BY user_id
HAVING total_years = (SELECT COUNT(*) FROM years)
 ;
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • is it possible withouth adding a new table – Regan Nov 22 '17 at 14:20
  • Isnt possible without the table. You need to know if one order match each year. – Juan Carlos Oropeza Nov 22 '17 at 14:21
  • what do i have to insert into the years table if i want 2010 - 2017 now i couldnt get it to work – Regan Nov 22 '17 at 14:51
  • i did that and i get a wrong result it shows me random customers that havent ordered in every year in that period – Regan Nov 22 '17 at 15:10
  • i have a column called c_type in my customer table is it possible to sort out all types that have value 0 within this query? – Regan Nov 23 '17 at 10:24
  • I already provide a demo where is working. Feel free to modify the demo so I can reproduce the wrong behavior. Once we solve this question create a new one so we can handle you c_type request. – Juan Carlos Oropeza Nov 23 '17 at 13:07