how to use 'BETWEEN' condetion two times in one sql statement for same column or diffrent columns

Question

this is sql statement give only one value

SELECT count(pn1) FROM pledges
WHERE date1
BETWEEN '2011-05-05' AND '2011-06-06'

i want use "between" more than one time for same column or for different columns in same table

for example i want use it in date1 more than one time or i want use it for date1 and date2 with different range

NOTE:I'm using java DB Derpy(JDBC-Derby)

Answer 1

More than once for the same column:

SELECT count(pn1) 
    FROM pledges
    WHERE date1 BETWEEN '2011-05-05' AND '2011-06-06'
       OR date1 BETWEEN '2011-07-05' AND '2011-08-06'

Two different columns:

SELECT count(pn1) 
    FROM pledges
    WHERE date1 BETWEEN '2011-05-05' AND '2011-06-06'
      AND date2 BETWEEN '2011-05-05' AND '2011-06-06'

EDIT: Based on comment, perhaps you're looking for something like this instead?

SELECT SUM(CASE WHEN date1 BETWEEN '2011-05-05' AND '2011-06-06' THEN 1 ELSE 0) END AS Count1,
       SUM(CASE WHEN date1 BETWEEN '2011-07-05' AND '2011-08-06' THEN 1 ELSE 0) END AS Count2
    FROM pledges
    WHERE date1 BETWEEN '2011-05-05' AND '2011-06-06'
       OR date1 BETWEEN '2011-07-05' AND '2011-08-06'