Group by multiple columns and case case

Question

I have to submit the orderdata group by month and card type during the year as follows:

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 

The table from which I can get the data has orders for each day of the month. I used the following SQL to group the number of orders 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 

)

Now I need to present this data in the form above using the case argument, since I have to group it by month, however I cannot do this. Maybe someone can help.

thanks

Answer 1

to group by month you can simply use:

SQLServer:

 group by year(dateField)+month(dateField) 

or Oracle:

 group by to_char(dateField, 'Year') + to_char(dateField, 'Month') 

so for your request:

 SELECT cardtype,COUNT (distinct(orderserialno)),year(dateField)+month(dateField) as order_date FROM tablename where orderdate between '01-jan-2012' and '31-dec-2012' GROUP BY cardtype,year(dateField)+month(dateField) 

Answer 2

 SELECT * FROM ( SELECT to_number(substr('201201', 1, 4)) curr_year , to_number(substr('201201', -2, 2)) curr_month , 'AMEX' card_type FROM dual UNION ALL SELECT to_number(substr('201202', 1, 4)) , to_number(substr('201202', -2, 2)) , 'DISC' FROM dual ) ORDER BY curr_year, curr_month, card_type / CURR_YEAR CURR_MONTH CARD_TYPE ------------------------------------ 2012 1 AMEX 2012 2 DISC 

Answer 3

You can change your GROUP BY as follows:

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

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