Summing up a total for a Year

Hi,

Seem to be getting stuck, I think there is an easy way to do what I
want, I have just not found it yet.
I have data for 10 years, I am looking to get a SUM of a field for a
particular year.

For example:

SELECT SUM(ounces) as ounces
FROM beverages

give me back one result, the total ounces for all 10 years.

If I do this:

SELECT date, SUM(ounces) as ounces
FROM beverages
WHERE YEAR(date) = 1998
GROUP BY date
ORDER BY date DESC

I get back multiple rows for the year 1998, when I really only want
the one value that is the total SUM for all ounces during the year
1998.

beverages has 4 fields, location, date, ounces and type.

thanks for any help!

O

On Apr 16, 3:01 pm, Barlymasher <theo...@gmail.com> wrote:
> Hi,
>
> Seem to be getting stuck, I think there is an easy way to do what I
> want, I have just not found it yet.
> I have data for 10 years, I am looking to get a SUM of a field for a
> particular year.
>
> For example:
>
> SELECT SUM(ounces) as ounces
> FROM beverages
>
> give me back one result, the total ounces for all 10 years.
>
> If I do this:
>
> SELECT date, SUM(ounces) as ounces
> FROM beverages
> WHERE YEAR(date) = 1998
> GROUP BY date
> ORDER BY date DESC
>
> I get back multiple rows for the year 1998, when I really only want
> the one value that is the total SUM for all ounces during the year
> 1998.
>
> beverages has 4 fields, location, date, ounces and type.
>
> thanks for any help!
>
> O

Also, I have tried


SELECT SUM(ounces) as ounces
FROM beverages
WHERE YEAR(date) = 1998
GROUP BY ounces



O

Barlymasher wrote:
> On Apr 16, 3:01 pm, Barlymasher <theo...@gmail.com> wrote:
>> Hi,
>>
>> Seem to be getting stuck, I think there is an easy way to do what I
>> want, I have just not found it yet.
>> I have data for 10 years, I am looking to get a SUM of a field for a
>> particular year.
>>
>> For example:
>>
>> SELECT SUM(ounces) as ounces
>> FROM beverages
>>
>> give me back one result, the total ounces for all 10 years.
>>
>> If I do this:
>>
>> SELECT date, SUM(ounces) as ounces
>> FROM beverages
>> WHERE YEAR(date) = 1998
>> GROUP BY date
>> ORDER BY date DESC
>>
>> I get back multiple rows for the year 1998, when I really only want
>> the one value that is the total SUM for all ounces during the year
>> 1998.
>>
>> beverages has 4 fields, location, date, ounces and type.
>>
>> thanks for any help!
>>
>> O
>
> Also, I have tried
>
>
> SELECT SUM(ounces) as ounces
> FROM beverages
> WHERE YEAR(date) = 1998
> GROUP BY ounces
>
>
>
> O

Try:
SELECT SUM(ounces) as ounces
FROM beverages
WHERE YEAR(date) = 1998
GROUP BY YEAR(date)

On Apr 16, 12:09 pm, Barlymasher <theo...@gmail.com> wrote:
> On Apr 16, 3:01 pm, Barlymasher <theo...@gmail.com> wrote:
>
>
>
> > Hi,
>
> > Seem to be getting stuck, I think there is an easy way to do what I
> > want, I have just not found it yet.
> > I have data for 10 years, I am looking to get a SUM of a field for a
> > particular year.
>
> > For example:
>
> > SELECT SUM(ounces) as ounces
> > FROM beverages
>
> > give me back one result, the total ounces for all 10 years.
>
> > If I do this:
>
> > SELECT date, SUM(ounces) as ounces
> > FROM beverages
> > WHERE YEAR(date) = 1998
> > GROUP BY date
> > ORDER BY date DESC
>
> > I get back multiple rows for the year 1998, when I really only want
> > the one value that is the total SUM for all ounces during the year
> > 1998.
>
> > beverages has 4 fields, location, date, ounces and type.
>
> > thanks for any help!
>
> > O
>
> Also, I have tried
>
> SELECT SUM(ounces) as ounces
> FROM beverages
> WHERE YEAR(date) = 1998
> GROUP BY ounces
>
> O

Just to clarify the "group by" clause -- you can not "sum" and "group
by" on the same column. It doesn't make any sense.

What you can do is "group by" something else. Such as, e.g:

SELECT sum(ounces) FROM beverages
WHERE [other filters]
GROUP BY type

Which might result something like:

type sum(ounces)
Coke 8383
Sprite 1004
OJ 92883

etc. Meaning that, of all the beverages, Coke summed up to 8,383
ounces. Etc.

Actually, if you really only want a single "sum" result for a single
year, you ought to be able to do this:

SELECT SUM(ounces) FROM beverages
WHERE YEAR(date) = 1998

As you describe the problem, "group by" shouldn't be necessary at all.