If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SQL language for complex sum
Hi, I am trying to write SQL language for a complex sum query. I have a
dataset with many columns, including PRECIP (precipitation for a specific month), MON (month), YEAR, and HRU. I want to find the total PRECIP between June and August (where MON between 6 and 8) for each HRU within each year. For example, for HRU=1 and YEAR=2002, I want to determine total PRECIP where MON between 6 and 8. I also want to determine total PRECIP for HRU=1 and YEAR=2003...and total PRECIP for HRU=2 and YEAR=2002, etc, etc. Can you help me with writing the proper language for this? Thanks |
#2
|
|||
|
|||
SQL language for complex sum
Try this --
SELECT YEAR, MON, HRU, Sum([PRECIP]) AS Precipitation FROM YourTable WHERE MON Between 6 And 8 GROUP BY YEAR, MON, HRU; "AK177" wrote: Hi, I am trying to write SQL language for a complex sum query. I have a dataset with many columns, including PRECIP (precipitation for a specific month), MON (month), YEAR, and HRU. I want to find the total PRECIP between June and August (where MON between 6 and 8) for each HRU within each year. For example, for HRU=1 and YEAR=2002, I want to determine total PRECIP where MON between 6 and 8. I also want to determine total PRECIP for HRU=1 and YEAR=2003...and total PRECIP for HRU=2 and YEAR=2002, etc, etc. Can you help me with writing the proper language for this? Thanks |
#3
|
|||
|
|||
SQL language for complex sum
"AK177" wrote in message
... Hi, I am trying to write SQL language for a complex sum query. I have a dataset with many columns, including PRECIP (precipitation for a specific month), MON (month), YEAR, and HRU. I want to find the total PRECIP between June and August (where MON between 6 and 8) for each HRU within each year. For example, for HRU=1 and YEAR=2002, I want to determine total PRECIP where MON between 6 and 8. I also want to determine total PRECIP for HRU=1 and YEAR=2003...and total PRECIP for HRU=2 and YEAR=2002, etc, etc. Can you help me with writing the proper language for this? Thanks It sounds like your SQLshould be something like this: SELECT [YEAR], HRU, Sum(PRECIP) As TotalPrecip FROM [YourTable] WHERE MON Between 6 And 8 GROUP BY [YEAR], HRU; Because YEAR is a reserved word, it *may* be necessary to prefix it with a table qualifier -- [YourTable].[YEAR] -- but I'm not sure about that. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#4
|
|||
|
|||
SQL language for complex sum
Thanks, this was very helpful. The SQL I used was:
Select YEAR, HRU, Sum([PRECIPmm]) As Precipitation from [mytable] where (MON Between 6 and 8) Group by YEAR, HRU Now, I have another question. I want to do a similar query on my table, but this time I want to sum seasonal snowfall. This means I need to group by seasons rather than by year but ofcourse I only have a month field not a season field. So, I want to group by months Nov of one year to May of the next year and get the sum of snowfall for this time period/season between 1998 and 2008. "Dirk Goldgar" wrote: "AK177" wrote in message ... Hi, I am trying to write SQL language for a complex sum query. I have a dataset with many columns, including PRECIP (precipitation for a specific month), MON (month), YEAR, and HRU. I want to find the total PRECIP between June and August (where MON between 6 and 8) for each HRU within each year. For example, for HRU=1 and YEAR=2002, I want to determine total PRECIP where MON between 6 and 8. I also want to determine total PRECIP for HRU=1 and YEAR=2003...and total PRECIP for HRU=2 and YEAR=2002, etc, etc. Can you help me with writing the proper language for this? Thanks It sounds like your SQLshould be something like this: SELECT [YEAR], HRU, Sum(PRECIP) As TotalPrecip FROM [YourTable] WHERE MON Between 6 And 8 GROUP BY [YEAR], HRU; Because YEAR is a reserved word, it *may* be necessary to prefix it with a table qualifier -- [YourTable].[YEAR] -- but I'm not sure about that. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#5
|
|||
|
|||
SQL language for complex sum
On Mon, 4 May 2009 12:49:01 -0700, AK177
wrote: Thanks, this was very helpful. The SQL I used was: Select YEAR, HRU, Sum([PRECIPmm]) As Precipitation from [mytable] where (MON Between 6 and 8) Group by YEAR, HRU Now, I have another question. I want to do a similar query on my table, but this time I want to sum seasonal snowfall. This means I need to group by seasons rather than by year but ofcourse I only have a month field not a season field. So, I want to group by months Nov of one year to May of the next year and get the sum of snowfall for this time period/season between 1998 and 2008. This would be one heck of a lot easier if you had a date/time field (with the 1st of each month) rather than separate fields, but... SELECT [Year], [HRU], Sum([PRECIPmm]) or your snowfall field FROM (SELECT [Year], [HRU], [PRECIPmm] FROM mytable WHERE [MON] IN (11, 12) AND [Year] = 1998 UNION ALL SELECT [Year] - 1, [HRU], [PRECIPmm] FROM mytable WHERE [MON] IN (1, 2, 3, 4, 5) AND [Year] = 1999) The UNION query will combine November-December from each year with January-May from the *NEXT* year, so (say) February 2001 data will be combined with December 2000 data, and displayed in the total as "the winter of 2000". -- John W. Vinson [MVP] |
#6
|
|||
|
|||
SQL language for complex sum
Agreed, it would be easier if I had date/time in one field, but that's not
how the data was given to me. This is a good start, but not quite right. .. How do I get a field for the season (e.g. "the winter of 2000" as you mentioned) - the sql you provided selects Year but Year is not relevant in the final query; I need WinterSeason or something. Also, I'm not quite sure I understand the Year-1 statement. Won't this just union year 1998 with year 1998 because it has "added" a year with "where year=1999) but "subtracted" a year with year-1? Btw, I added Group by HRU (and will need to add another Group by WinterSeason) Thanks. "John W. Vinson" wrote: On Mon, 4 May 2009 12:49:01 -0700, AK177 wrote: Thanks, this was very helpful. The SQL I used was: Select YEAR, HRU, Sum([PRECIPmm]) As Precipitation from [mytable] where (MON Between 6 and 8) Group by YEAR, HRU Now, I have another question. I want to do a similar query on my table, but this time I want to sum seasonal snowfall. This means I need to group by seasons rather than by year but ofcourse I only have a month field not a season field. So, I want to group by months Nov of one year to May of the next year and get the sum of snowfall for this time period/season between 1998 and 2008. This would be one heck of a lot easier if you had a date/time field (with the 1st of each month) rather than separate fields, but... SELECT [Year], [HRU], Sum([PRECIPmm]) or your snowfall field FROM (SELECT [Year], [HRU], [PRECIPmm] FROM mytable WHERE [MON] IN (11, 12) AND [Year] = 1998 UNION ALL SELECT [Year] - 1, [HRU], [PRECIPmm] FROM mytable WHERE [MON] IN (1, 2, 3, 4, 5) AND [Year] = 1999) The UNION query will combine November-December from each year with January-May from the *NEXT* year, so (say) February 2001 data will be combined with December 2000 data, and displayed in the total as "the winter of 2000". -- John W. Vinson [MVP] |
#7
|
|||
|
|||
SQL language for complex sum
On Tue, 5 May 2009 06:00:01 -0700, AK177
wrote: Agreed, it would be easier if I had date/time in one field, but that's not how the data was given to me. This is a good start, but not quite right. .. How do I get a field for the season (e.g. "the winter of 2000" as you mentioned) - the sql you provided selects Year but Year is not relevant in the final query; I need WinterSeason or something. Also, I'm not quite sure I understand the Year-1 statement. Won't this just union year 1998 with year 1998 because it has "added" a year with "where year=1999) but "subtracted" a year with year-1? The "Year" field in the UNION query is indeed the "winter season". The first SELECT clause of the UNION query selects (say) November and December of 2000. The second clause selects January through May of 2001, and the -1 lets you combine that 2001 data with the 2000 data. You could alias the field as WinterSeason if you want: SELECT [WinterSeason], [HRU], Sum([PRECIPmm]) FROM (SELECT [Year] AS WinterSeason, [HRU], [PRECIPmm] FROM mytable WHERE [MON] IN (11, 12) AND [Year] = 1998 UNION ALL SELECT [Year] - 1 AS WinterSeason, [HRU], [PRECIPmm] FROM mytable WHERE [MON] IN (1, 2, 3, 4, 5) AND [Year] = 1999) -- John W. Vinson [MVP] |
#8
|
|||
|
|||
SQL language for complex sum
Thanks, I really appreciate all your help! "John W. Vinson" wrote: On Tue, 5 May 2009 06:00:01 -0700, AK177 wrote: Agreed, it would be easier if I had date/time in one field, but that's not how the data was given to me. This is a good start, but not quite right. .. How do I get a field for the season (e.g. "the winter of 2000" as you mentioned) - the sql you provided selects Year but Year is not relevant in the final query; I need WinterSeason or something. Also, I'm not quite sure I understand the Year-1 statement. Won't this just union year 1998 with year 1998 because it has "added" a year with "where year=1999) but "subtracted" a year with year-1? The "Year" field in the UNION query is indeed the "winter season". The first SELECT clause of the UNION query selects (say) November and December of 2000. The second clause selects January through May of 2001, and the -1 lets you combine that 2001 data with the 2000 data. You could alias the field as WinterSeason if you want: SELECT [WinterSeason], [HRU], Sum([PRECIPmm]) FROM (SELECT [Year] AS WinterSeason, [HRU], [PRECIPmm] FROM mytable WHERE [MON] IN (11, 12) AND [Year] = 1998 UNION ALL SELECT [Year] - 1 AS WinterSeason, [HRU], [PRECIPmm] FROM mytable WHERE [MON] IN (1, 2, 3, 4, 5) AND [Year] = 1999) -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|