I have a table with four columns
DAY |MONTH | YEAR | SUM_FILE_SIZE
----------------------------------------
How do i select sum_file_size from this table for a date range between 'day 1 month 7 year 2013' to 'day 17 month 9 year 2014'?
I have tried this query and it is giving incorrect data.
select day, month, year, sum_file_size
from bermuda_stat.fileandcite_daily
where day between 1 and 17 and month between 7 and 9 and year between 2013 and 2014
group by day, month, year
order by year, month, day.
How do i get all the sum_file_size between this two date range?
Please note that it is excluding all days other than whatever dates falls between 1 and 17 ,similarly it is excluding all months other than whatever months falls between 7 and 9?
Thanks.
Please see this list of helpful date and time functions. The first one you should notice(for this problem) is the MAKEDATE() function. If you give this a constant 1, it can be used to get the first day of a given year. So, for you example this would be:
SELECT MAKEDATE(year, 1) AS firstDayOfYear
FROM myTable
Now, you can use the ADDDATE() function to add the rest of your information. You can add the day like this:
SELECT ADDDATE(MAKEDATE(year, 1), INTERVAL (day - 1) DAY) AS dayAndYear
FROM myTable;
The reason I used (day-1) is because we already have a day of 1 from makedate. So 01-01 + 3 gives you 01-04 which is incorrect.
The month part works the same way, building on the last date.
SELECT ADDDATE(ADDDATE(MAKEDATE(year, 1), INTERVAL (day - 1) DAY), INTERVAL (month - 1) MONTH) AS fullDate
FROM myTable
Now, you can implement that into your where statement. Since you know the dates you want to restrict, you can treat those like dates. The final query looks like this:
SELECT SUM(filesize)
FROM myTable
WHERE (ADDDATE(ADDDATE(MAKEDATE(year, 1), INTERVAL (day - 1) DAY), INTERVAL (month - 1) MONTH))
BETWEEN '2013-07-01' AND '2014-09-17';
Here is an SQL Fiddle example. I left each part in there, so you can see how the breakdown works step by step.
This question might also be helpful.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments