calculate amount on dates difference

Question

i have one table with two columns as shown in picture

at the execution of page user enter the start and end date.

now what i need to calculate is total amount.

for example, if user put

      start date: 18-jan-2010
      end date: 23-jan-2010

then he has 2 dates in first options (20$) and 3 dates in second options (26$) so total will be (56$).

first i want to calculate in sql

1. how many days in first range (if any this depends on supplied dates from user)
2. how many days in 2nd range  (if any this depends on supplied dates from user)
3. how many days in 3rd range  (if any this depends on supplied dates from user)

then

we will multiply days with Amount in php for each range.

at the end i want to get grand total also in php.

Thanks

Answer 1

Maybe:

SELECT SUM(amount) FROM your_table WHERE date BETWEEN start_date AND end_date GROUP BY user

Answer 2

SELECT 'In Range' AS SumType
       SUM(amount) 
  FROM <table> 
 WHERE date BETWEEN start_date AND end_date
UNION
SELECT 'Outside Range' AS SumType
       SUM(amount) 
  FROM <table> 
 WHERE date < start_date OR date > end_date
UNION
SELECT 'Grand Total' AS SumType
       SUM(amount) 
  FROM <table>