时间戳

首页 > 解决方案 > 按产品类别划分的销售额增长

mysql - 按产品类别划分的销售额增长

问题描述

我需要帮助才能找到去年按产品类别划分的销售额增长

表看起来像这样

Tablename- SUPERSTOREDATABASE   

|----------|---------------|--------|-------------|----------|-----------|-----------|
| OrderID  |ProductCategory| Date   |ProductName  | Sales    |Quantity   | Profit    |
|----------|---------------|--------|-------------|----------|-----------|-----------|
|    12    |   Furniture   |2/2/2018|    Table    |     $30  |    2      |      10   |
|----------|---------------|--------|-------------|----------|-----------|-----------|

返回表：我正在尝试排除重新调整的项目

|------------------|-------------------|
| OrderID          |     Returned      |
|------------------|-------------------|
|    2             |        Yes        |
|------------------|-------------------|

我需要写查询来获得这个输出


|------------------|-------------------|
| ProductCategory  |Year on Year Growth|
|------------------|-------------------|
|    Furniture     |        35%        |
|------------------|-------------------|

请帮我写一个查询

我尝试了下面的代码，但它不起作用

select year(ORDERS.ORDERDATE) as year,
      sum(case when year(ORDERS.ORDERDATE) = 2019 then ORDERS.SALES else 0 end) as price_2019,
      sum(case when year(ORDERS.ORDERDATE) = 2018 then ORDERS.SALES else 0 end) as price_2018
from  SUPERSTOREDATABASE.PUBLIC.ORDERS
left join SUPERSTOREDATABASE.PUBLIC.RETURN on ORDERS.ORDERID = RETURN.ORDERID
group by year(ORDERS.ORDERDATE)
order by max(year(ORDERS.ORDERDATE));

标签： mysqlsql

解决方案

我需要帮助才能找到去年按产品类别划分的销售额增长

如果我理解正确，您只需要调整您对产品类别的查询：

select o.product_category
       sum(case when year(o.ORDERDATE) = 2019 then o.SALES else 0 end) as price_2019,
       sum(case when year(o.ORDERDATE) = 2018 then o.SALES else 0 end) as price_2018,
        (-1 +
         sum(case when year(o.ORDERDATE) = 2019 then o.SALES else 0 end) /
         sum(case when year(o.ORDERDATE) = 2018 then o.SALES else 0 end)
        ) as yoy_growth
from  SUPERSTOREDATABASE.PUBLIC.ORDERS o
group by o.product_category
order by o.product_category

推荐阅读