Re: Selecting and ordering data over multiple date ranges

To get a cross tab, you can use the following technique. Only one query is needed.

SELECT state, 
sum(IF(month(identifier)=1,1,0)) as 'Jan',
sum(IF(month(identifier)=2,1,0)) as 'Feb',
sum(IF(month(identifier)=3,1,0)) as 'Mar',
count(*) as TOTAL
from contacts
WHERE state != ''
GROUP BY state;
[download]

And you should get a result like :

+----------+-----+-----+----+-------+
| state    | Jan | Feb | Mar| Total |
+----------+-----+-----+----+-------+
| NY       |  10 | 12  | 11 |    33 |
| IL       |  15 | 14  | 15 |    44 |
| TX       |  25 | 20  | 22 |    67 |
+----------+-----+-----+----+-------+
[download]

Check this article for a step-by-step tutorial on how to use Perl to get get the values for your columns in the query without doing it manually. The article is specific for MySQL, but the algorithm works on any DBMS.

 _  _ _  _  
(_|| | |(_|><
 _|
[download]

Comment on Re: Selecting and ordering data over multiple date ranges Select or Download Code

Replies are listed 'Best First'.
Re: Re: Selecting and ordering data over multiple date ranges by peppiv (Curate) on Feb 07, 2003 at 18:55 UTC
Once again gmax you've made my day better. Check it out: `"SELECT state, SUM(case when identifier BETWEEN '2003-01-01' AND '2003 +-02-01' then 1 else 0 end) AS 'Jan', SUM(case when identifier BETWEEN + '2003-02-01' AND '2003-03-01' then 1 else 0 end) AS 'Feb', COUNT() +as TOTAL FROM contacts WHERE state != '' GROUP BY state ORDER BY 3 DE +SC"` [download] The 'case' instead of IF works because it's on MSQl. Also, it requires the 'then 1 else 0 end'. Works like a charm! Soooopa Thanx peppiv*	[reply] [d/l]

Replies are listed 'Best First'.

Re: Re: Selecting and ordering data over multiple date ranges
by peppiv (Curate) on Feb 07, 2003 at 18:55 UTC

gmax

"SELECT state, SUM(case when identifier BETWEEN '2003-01-01' AND '2003
+-02-01' then 1 else 0 end) AS 'Jan', SUM(case when identifier BETWEEN
+ '2003-02-01' AND '2003-03-01' then 1 else 0 end) AS 'Feb', COUNT(*) 
+as TOTAL FROM contacts WHERE state != '' GROUP BY state ORDER BY 3 DE
+SC"
[download]

peppiv

[reply]
[d/l]