http://www.perlmonks.org?node_id=233511


in reply to Selecting and ordering data over multiple date ranges

I was thinking you might be able to get the headings as desired by using in-line views if your DB supports them. I tried this sort of thing against an Oracle DB here. But it was taking so long I killed it:

SELECT o.state, dec.total, jan.total, feb.total, count(*) FROM ( SELECT state, to_char(order_date, 'Mon'), count(*) total FROM web_order WHERE order_date BETWEEN '01-DEC-02' AND '31-DEC-02' GROUP BY state, to_char(order_date, 'Mon') ) dec, ( SELECT state, to_char(order_date, 'Mon'), count(*) total FROM web_order WHERE order_date BETWEEN '01-JAN-03' AND '31-JAN-03' GROUP BY state, to_char(order_date, 'Mon') ) jan, ( SELECT state, to_char(order_date, 'Mon'), count(*) total FROM web_order WHERE order_date BETWEEN '01-FEB-03' AND '28-FEB-03' GROUP BY state, to_char(order_date, 'Mon') ) feb, web_order o WHERE o.order_date BETWEEN '01-DEC-02' AND '28-FEB-03' GROUP BY o.state, dec.total, jan.total, feb.total

As an alternative, it may be easier to do the state/month grouping and reformat yourself after getting the rows the DB hands back. I personally find Perl much better at more complex reformatting than most DB's.