Question 20378 in Mysql Question posted in
The official MySQL documentation can be found here.

aggregating sql tables in a query by distinct – Mysql

I have three tables in sql

order_table (order_id, product_id, amount) 

products_table (product_id, name, price_usd),

all_orders (order_id, customer_id, order_date, total_amount),

I would like to produce a sql query which outputs the total revenue for each product by month for the last 12 months.

I somehow need to seperate out the distinct products, but I am not sure how to construct such a query.

any pointers would be great

Tags:

2

Answers


  1. 0

    You need to create base_table first in which it contain all necessary columns. you can use CTE to create to create this using with. After that, you can sum the column you wish and end it using where date >= date_sub(current_date(), interval 12 month)

    the general query is as follow :

    with base as (SELECT
  all_orders.order_id,
  date_trunc(all_orders.order_date,month) as order_date,
  all_orders.total_amount as quantity
  name as product_name,
  price_usd * total_amount as revenue
FROM
  all_orders
LEFT JOIN
  order_table
USING
  (order_id)
LEFT JOIN 
  products_table 
USING 
  (product_id))

select order_date, product_name, sum(total_amount) as total_quantity, sum(revenue) as total_revenue from base 
where order_date >= date_sub(current_date(), interval 12 month)
group by 1,2

    Do note that I’m using Google SQL Dialect. It might be a little bit different in the dialect that you are currently using

    Login or Signup to reply.
  2. 0

    The answer given is using a WITH...., which is not needed:

    select 
   DATE_FORMAT(all_orders.order_date, '%Y-%m') as order_date, 
   name as product_name, 
   sum(total_amount) as total_quantity, 
   sum(price_usd * total_amount) as total_revenue 
from all_orders
LEFT JOIN order_table USING (order_id)
LEFT JOIN products_table USING (product_id)
where all_orders.order_date >= date_sub(current_date(), interval 12 month)
group by 1,2 ;

    should do the same (untested)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search