I have a sales table in SQLite:

I want to get the total unit_solds for each customer on the first date and last date of their purchases. My query is:

  select customer_id,
         sum(units_sold) total_units_sold
    from sales
group by customer_id
  having purchase_date = min(purchase_date) 
      or purchase_date = max(purchase_date)

I was expecting results like:

but I got:

I would like to know why this solution doesn't work.

CodePudding user response：

The order of the phrase is incorrect Note: The having statement is executed after compilation.

You need to get the results as partial queries For example, I arranged to know the first line of the date according to each customer as well as the last line of the date (by getting the first line after descending order) and then execute the group statement

The example is complete

      select customer_id,sum(units_sold) from (
         select customer_id, units_sold,purchase_date,
            ROW_NUMBER() over(partition by customer_id order by purchase_date) As RowDatefirst,
            ROW_NUMBER() over(partition by customer_id order by purchase_date desc)As RowDatelast    
         from sales
         ) t where t.RowDatefirst = 1 or t.RowDatelast=1
       group by customer_id

CodePudding user response：

Try this:

 SELECT a.customer_id, SUM(a.units_sold) as total_units_sold
FROM sales a
INNER JOIN (
       SELECT customer_id, MIN(purchase_date) as _first ,MAX(purchase_date) as _last
       FROM sales
       GROUP BY customer_id
) b ON a.customer_id = b.customer_id AND
      (a.purchase_date = b._first OR a.purchase_date = b._last)
GROUP BY a.customer_id

http://sqlfiddle.com/#!7/0a4a4/7