在列中获得多年的销售总和 [英] Get SUM of sales for multiple years in columns

问题描述

我想得到这样的东西:

*Customer    *2009     *2010    *
|------------|---------|--------|
|Peter       |120      |240     |
|Johe        |455      |550     |

我查询的第一个方法是:

My first approach to the query was this:

Select c.name, sum(o2009.price), sum(o2010.price) from customer c
join orders o2009 on (o2009.customerId = c.id AND o2009.year = 2009)
join orders o2010 on (o2010.customerId = c.id AND o2010.year = 2010)
group by c.id

不幸的是，这是完全错误的.我想我可以运行2个查询，然后建立一个并集，但是也许有一些更简单的东西吗?

Unfortunately this is completely wrong. I guess I could run 2 queries and then build a union, but maybe there is something simpler?

推荐答案

只需修改答案之一即可获得没有订单的客户-

Just modifying one of the answers to get customers with no orders -

Select c.name, 
   Sum(Case When o.year == 2008 Then price Else 0 End) cy2008,
   Sum(Case When o.year == 2009 Then price Else 0 End) cy2009,
   Sum(Case When o.year == 2010 Then price Else 0 End) cy2010
From Customers c left outer join 
     Orders o on o.customer_id = c.customer_id
Group By c.name

这篇关于在列中获得多年的销售总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！