按多列和案例陈述分组 [英] Group by Multiple columns and case statement
问题描述
我必须像这样按年份和月份显示卡类型的订单数据组:
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屋!