按多列和案例陈述分组 [英] Group by Multiple columns and case statement

查看:71
本文介绍了按多列和案例陈述分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我必须像这样按年份和月份显示卡类型的订单数据组:

I have to present the orderdata group by Month and card type for an year like this:

MONTH   CARDTYPE ORDER_CNT
201201  AMEX    10,835
201201  DISC    6,542
201201  MC  2,675
201202  AMEX    66,817
201202  DISC    36,581
201202  MC  165,683

我可以从中获取数据的表在每月的每一天都有订单.我使用以下SQL来按卡类型对当月每一天的订单计数进行分组:

The table from which I can get the data has orders by each day of the month. I used the following SQL to group the order count for each day of the month by card type:

(

SELECT cardtype,orderdate,COUNT (distinct(orderserialno)) FROM tablename
where orderdate between '01-jan-2012' and '31-dec-2012'
GROUP BY cardtype,orderdate

)

现在,我需要使用case语句以上述形式显示此数据,因为我必须按月对它进行分组,但是我无法做到这一点.有人可以帮忙吗.

Now I need to present this data in the above form by using case statement as I will have to group it by month, however I am not able to do that. Can someone please help.

谢谢

推荐答案

您可以像这样修改GROUP BY部分:

You can modify your GROUP BY section like this :

GROUP BY cardtype, trunc(orderdate, 'MM')

,在SELECT部分中,您应该使用to_char(orderdate, 'YYYYMM')而不是orderdate

and in the SELECT section you should use to_char(orderdate, 'YYYYMM') instead of orderdate

这篇关于按多列和案例陈述分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆