/*
Task: We need the query for daily total report. Report should contain list of ordered courses (name and cost), count for each course, cost subtotal for each course and a total cost. Data should be queried on the daily basis.
Optionally - data should be queried using the dates range, between Start date and End date.
*/

--View creation
CREATE OR REPLACE VIEW "FoodOrder".daily_order_report AS
 SELECT oc.order_date,
    co.title,
    co.price,
    sum(oc.portions) AS portions,
    sum(oc.portions)::numeric * co.price AS cost,
    cu.first_name,
    cu.last_name
   FROM course as co
     JOIN order_course as oc ON co.id = oc.course_id
     JOIN customer as cu ON cu.id = oc.customer_id
  GROUP BY oc.order_date, co.id, oc.course_id, cu.first_name, cu.last_name;

--Select query
select title, price, portions, cost
from daily_order_report
where order_date IN('2001-02-16') --or BETWEEN begin_date AND end_date
UNION
select 'Total', 0, 0, sum(cost)
from daily_order_report
where order_date IN('2001-02-16') --or BETWEEN begin_date AND end_date
order by 4