SQL language for complex sum

  • Thread starter Thread starter AK177
  • Start date Start date
A

AK177

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
 
Try this --
SELECT YEAR, MON, HRU, Sum([PRECIP]) AS Precipitation
FROM YourTable
WHERE MON Between 6 And 8
GROUP BY YEAR, MON, HRU;
 
AK177 said:
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
Click to expand...


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.
 
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 said:
AK177 said:
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
Click to expand...


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)
Click to expand...
 
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.
Click to expand...

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".
 
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 said:
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.
Click to expand...

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".
Click to expand...
 
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?
Click to expand...

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)
 
Thanks, I really appreciate all your help!

John W. Vinson said:
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?
Click to expand...

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)
Click to expand...
 
Back
Top